linked server variable

• Gators79

  SSC Enthusiast

  Points: 108

  More actions

  June 14, 2010 at 8:52 am

  #222190

  I'm trying to pass in a variable

  to a store procedure. Based on the variable the s/p will look at the linked server

  DECLARE @my_LinkedServer

  SELECT COUNT(brands)

  FROM @my_LinkedServer.Linkedserverlocal.dbo.brands

  I get the error Incorrect syntax near '.'.

  However if I hard code the @my_LinkedServer it works fine

• jjtetzlo

  SSCrazy

  Points: 2511

  More actions

  June 14, 2010 at 9:18 am

  #1179946

  You can use dynamic sql to accomplish this:

  declare @my_LinkedServer varchar(100)

  declare @sql varchar(255)

  set @my_LinkedServer = 'Server1'

  set @sql = 'select count(brands) from [' + @my_LinkedServer + '].[Linkedserverlocal].[dbo].[brands]'

  print @sql

  --exec (@sql)

  - Jeff

• Gators79

  SSC Enthusiast

  Points: 108

  More actions

  June 14, 2010 at 9:22 am

  #1179947

  Excellent!

  Worked great Thanks