How to run queries on multiple linked servers efficiently?
I have to run several queries on a set of linked servers. I want to try and do it without resorting to dynamic sql, but having trouble so far.
Here is what I have so far:
SELECT @serverName = Name FROM #servers WHERE ID = @minID
EXEC sp_addlinkedserver @server=@serverName, @srvproduct='SQL Se开发者_如何学Crver'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'lani-radio001', @locallogin = NULL , @useself = N'False', @rmtuser = N'user1', @rmtpassword = N'Password1'
declare @sql varchar(255)
SET @sql = 'SELECT * from [' + @serverName + '].mydb.dbo.mytable'
EXEC (@sql)
I have to run the above code in a loop against multiple servers (which are all in #servers table). How can I pull this off without resorting to dynamic SQL?
P.S. All servers in question are SQL Server 2005.
What about this?:
SELECT * FROM OPENQUERY(@serverName,'select * from mydb.dbo.mytable');
精彩评论