I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:
exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go
While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):
while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end
This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.
Is there some way I could dynamically create the four part name and execute it?
Any assistance would be greatly appreciated.
Thanks, Mark
DECLARE @.server nvarchar(128)
DECLARE @.cmd nvarchar(1000)
SET @.server = 'MyServer'
SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'
EXEC (@.cmd)
No comments:
Post a Comment