dynamic linked server query

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  November 12, 2008 at 11:05 pm

  #99365

  hi ,

  Below is the linked server query ,here i m passing the server and linked server name dynamically

  The problem is that its giving error for very last line (it is not taking linked server name dynamically)

  but if i hard coded the linked server in last line its working

  i need to have linked server dynamically

  ----------------------------------------------------------------------------

  declare @servername varchar(20),

  @Linked_server varchar(20),

  @SQLCmd varchar(200),

  @TableName varchar(50)

  set @TableName = 'sysobjects'

  SELECT @servername = @@servername

  IF LEFT(@servername,2) ='D2'

  SET @Linked_server = 'IN\DEV'

  ELSE

  SET @Linked_server = 'D2\DEV'

  print @servername

  print @Linked_server

  SET @SQLCmd = 'SELECT top 10 * from [' + @servername + '].PROD.dbo.' + @TableName + ' WITH (NOLOCK)'

  EXEC(@SQLCmd) AT @Linked_server

  ------------------------------------------------------------------------------

  Please help

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  November 13, 2008 at 7:39 am

  #898087

  Try without AT @Linked_Server, it will work, its working for me, what version of SQL are u using mate???

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  August 28, 2011 at 9:37 am

  #1374299

  Don't suppose you know how to see SYNONYMS in a linked server.

  I have SYNONYMS set up on server b and on server a set the user with view definition of SYNONYMS but cannot see them in linked server only see tables and views. Can't see SP either

  Right now i can ony set up a view to see the data on servera and want the user to be able to run select * from mysynonym

  Just curious if even possible.

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  August 30, 2011 at 2:27 am

  #1374846

  Bhuvnesh (11/12/2008)

  ---

  hi ,

  Below is the linked server query ,here i m passing the server and linked server name dynamically

  The problem is that its giving error for very last line (it is not taking linked server name dynamically)

  but if i hard coded the linked server in last line its working

  i need to have linked server dynamically

  ----------------------------------------------------------------------------

  declare @servername varchar(20),

  @Linked_server varchar(20),

  @SQLCmd varchar(200),

  @TableName varchar(50)

  set @TableName = 'sysobjects'

  SELECT @servername = @@servername

  IF LEFT(@servername,2) ='D2'

  SET @Linked_server = 'IN\DEV'

  ELSE

  SET @Linked_server = 'D2\DEV'

  print @servername

  print @Linked_server

  SET @SQLCmd = 'SELECT top 10 * from [' + @servername + '].PROD.dbo.' + @TableName + ' WITH (NOLOCK)'

  EXEC(@SQLCmd) AT @Linked_server

  ------------------------------------------------------------------------------

  Please help

  Use it Like this

  ------------------------------------------

  declare @servername varchar(100),

  @Linked_server varchar(100),

  @SQLCmd varchar(200),

  @TableName varchar(100)

  set @TableName = 'sysobjects'

  SELECT @servername = @@servername

  --IF LEFT(@servername,2) ='D2'

  --SET @Linked_server = 'IN\DEV'

  --ELSE

  SET @Linked_server = '[xx.xxx.x.xxx]'

  print @servername

  print @Linked_server

  SET @SQLCmd = '(''SELECT top 10 * from abc.dbo.' + @TableName + ' WITH (NOLOCK)'') AT ' + @Linked_server

  select @SQLCmd = 'EXEC ' + (@SQLCmd)

  EXEC(@SQLCmd)

  Regards

  Ashok

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  August 30, 2011 at 7:40 am

  #1374999

  Thanks for the script

• RS7

  Valued Member

  Points: 67

  More actions

  April 12, 2016 at 1:22 am

  #1871311

  This is not working for me, can you suggest what is the issue in below code:

  DECLARE @myCount int;

  DECLARE @sql1 nvarchar(MAX);

  DECLARE @DBAT [varchar](10) = 'LS_EG_DEV'

  DECLARE @sql nvarchar(max);

  set @sql =

  N'BEGIN

  SELECT count(*) into :mCount FROM OWNER_CDG.CLI_CLIENT;

  END;'

  SET @sql1 = N'(@sql, @myCount OUTPUT) at ' + @DBAT

  SELECT @sql1

  SELECT @sql1 = 'EXEC ' + ( @sql1)

  SELECT @sql1

  EXEC (@sql1)