Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked ServerName as variable Expand / Collapse
Author
Message
Posted Sunday, February 3, 2008 10:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 25, 2008 12:13 AM
Points: 12, Visits: 39
Hi,

How can I pass the linked servername as variable in the stored porcedure.?

I have multiple linked servers having identical databases and I have to use this SP to fetch data from them.

the statement in SP is as below :
select invoicedate,invoiceno,invoicenet from [SVR1].TestPOS.dbo.invoice_header

how can I replace [SVR1] with a variable..?

thanks in advance

regards,

Thomas George
Post #451007
Posted Sunday, February 3, 2008 11:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 2,278, Visits: 3,054
You have to use dynamic sql for this one.

e.g.
DECLARE @SQLString nvarchar(500),
@srv VARCHAR(25)

SET @srv = 'MyServerName'
SET @SQLString = N'
SELECT
invoicedate,
invoiceno,
invoicenet
FROM [' + @srv + '].TestPOS.dbo.invoice_header
'
EXECUTE sp_executesql @SQLString





My blog: http://jahaines.blogspot.com
Post #451015
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse