Linked ServerName as variable

• Thomas George-390542

  Old Hand

  Points: 318

  More actions

  February 3, 2008 at 10:37 pm

  #181621

  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 :

  [font="Courier New"]select invoicedate,invoiceno,invoicenet from [SVR1].TestPOS.dbo.invoice_header[/font]

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

  thanks in advance

  regards,

  Thomas George

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  February 3, 2008 at 11:24 pm

  #774641

  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