Cursor fetch on linked Servers

Question

Post reply

Cursor fetch on linked Servers

Si

SSCommitted

Points: 1537
More actions
August 7, 2006 at 8:51 am

#115949

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

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

Si SSCommitted Points: 1537 More actions · Answer 2

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

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?

Si SSCommitted Points: 1537 More actions · Answer 4

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.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

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

Si SSCommitted Points: 1537 More actions · Answer 6

That seems to have done the trick!! Thanks ever so much for your persistence Jo

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 7

Jo Pattyn

SSC-Dedicated

Points: 32347

August 11, 2006 at 7:37 am

#654539

You're welcome.