Wednesday, March 7, 2012

Execute SP on linked server?

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

No comments:

Post a Comment