I've created a stored procedure on several servers. All with the same name and all in a database with same names.
Now Im trying to execute the stored procedures by executing a script... I dont get it to work!
I can execute it successfully by starting it manually:
exec [CIMPDB01\CIMT1].zz_am.dbo.usp_SpaceMon
exec [CIMPDB01\CIMT2].zz_am.dbo.usp_SpaceMon
and so on.... no problem
But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it wont:
declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1
create table #databases
(ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances where actief='J'
while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL='exec ' + @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL
set @.x = @.x + 1
end
drop table #databases
Msg 203, Level 16, State 2, Line 17
The name 'exec [CIMPDB01\CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.
Please help...declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1
create table #databases
(ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances where actief='J'
while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL= @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL
set @.x = @.x + 1
end
drop table #databases
Don't need to use 'exec'.
Hope this helps,
David
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment