Cursor fetch on linked Servers
-
Hi, can someone please let me know how I can code a select statement within a cursor that can select from the same table name across a number of linked servers,without it pulling the results back from the local server.
So far I have a simple cursor coded as :-
DECLARE @server_id nchar(10)
DECLARE server_cursor CURSOR FAST_FORWARD FOR
SELECT servername from local_server -- table holding all server name
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @server_id
WHILE @@FETCH_STATUS = 0
BEGIN
select @server_id as [Server],
plunum,
pludesc,
from backoff.dbo.plu -- this is the area causing the problem
FETCH NEXT FROM server_cursor INTO @server_id
END
This gives results back but repeatedly performs the query locally, so returns the same result set for each row in 'local_server'
If instead of 'from backoff.dbo.plu' I code '@server_id.from backoff.dbo.plu' it doesnt like the syntax and is clearly not an option. So I wanted to find out how to do this bearing in mind there are upwards of 100 servers to query.
Thanks very much
-
You will need dynamic sql for this
DECLARE @Statement nvarchar(2000)
SET @Statement =N'SELECT '+ convert(nvarchar(150),@server_id) + N' as [Server] ,
plunum,
pludesc,
from '+ convert(nvarchar(150),@server_id) + N'.backoff.dbo.plu'
EXECUTE master.dbo.sp_executesql @Statement
-
Thanks for that Jo,
The problem with this is that the set @statement is creating a statement such as
select ust980101 as [Server], plunum, pludesc etc.... Instead of swapping the @server_id for the actual server id. So its coming back saying ust980101 is an invalid column. Is there a way of coding it so it takes the value of server_id and uses it at run time rather than take it as a literal when compiling the statement. Hope thats clear ?
Thank-you
-
You can fill the server variable with a loop. (cursor) SET @ServerID=@OtherVariable
Can you write a couple of statements of wanted output for the statement to execute?
-
Hi Jo, thanks for your help on this.
Excuse my ignorance but I dont quite understand what you mean by the first statement (ie filling the variable with a loop?)
What I'm looking for if I ran it locally would be :-
select @@servername,
plunum,
pludesc
from backoff.dbo.plu
This will work locally, but once you run it against a linked server, it will still pick up the result of @@servername from the local server.
I need something where I can run a query from the 'plu' table on each server, but at the same time add the linked server name that it ran against. If I don't add the server, the results can be the same and I cant differentiate the rows by server.
-
Something like this?
/*WILL hold names of linked servers*/
CREATE table #linkedservers
(
SRV_NAME sysname NULL /*Name of the linked server. */
,SRV_PROVIDERNAME nvarchar(128) NULL/*Friendly name of the OLE DB provider managing access to the specified linked server. */
,SRV_PRODUCT nvarchar(128) NULL/*Product name of the linked server. */
,SRV_DATASOURCE nvarchar(4000) NULL/* OLE DB data source property corresponding to the specified linked server. */
,SRV_PROVIDERSTRING nvarchar(4000) NULL/* OLE DB provider string property corresponding to the linked server. */
,SRV_LOCATION nvarchar(4000) NULL /*OLE DB location property corresponding to the specified linked server. */
,SRV_CAT sysname NULL
)
INSERT #linkedservers
(SRV_NAME
,SRV_PROVIDERNAME
,SRV_PRODUCT
,SRV_DATASOURCE
,SRV_PROVIDERSTRING
,SRV_LOCATION
,SRV_CAT
)
EXEC sp_linkedservers
DECLARE @Statement nvarchar(2000) /*to hold SQL-STATEMENT*/
DECLARE @server_id nvarchar(150)
DECLARE curmyLinkedServers CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT SRV_NAME
FROM #linkedservers
ORDER BY SRV_NAME
OPEN curmyLinkedServers
FETCH NEXT FROM curmyLinkedServers into @server_id
WHILE @@FETCH_STATUS=0
BEGIN
/*building up dynamic sql*/
SET @Statement =N'SELECT '''+ convert(nvarchar(150),@server_id) + N''' as [Server] ,
plunum,
pludesc,
from '+ convert(nvarchar(150),@server_id) + N'.backoff.dbo.plu'
/*executing dynamic sql using format linkedserver.database.owner.object*/
EXECUTE master.dbo.sp_executesql @Statement
FETCH NEXT FROM curmyLinkedServers into @server_id
END
CLOSE curmyLinkedServers
DEALLOCATE curmyLinkedServers
DROP TABLE #linkedservers
-
That seems to have done the trick!! Thanks ever so much for your persistence Jo
-
You're welcome.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply