Link servers and using a variables

• Linda-272642

  Ten Centuries

  Points: 1100

  More actions

  August 24, 2006 at 3:40 pm

  #173787

  I have a link server setup with the following set to true: Collation Compatible, DataAccess, RPC, RPC Out

  The issue is that when running the query below

  SELECT name, address, street FROM table1 WHERE name = Linda

  it resolves the query on the remote server

  but when using a variable the WHERE clause is not resolved on the remote server so the entire table is moved to the local server

  Is there a way or a place to configure sql server to do all the work on the remote server and not on local server when using a variable

   

  Thanks for all your help

   

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  August 24, 2006 at 3:46 pm

  #656732

  Use dynamic SQL, so that the variable's value is passed to the linked server:

  Declare @SQL varchar(4000)

  Select @SQL = 'SELECT * FROM [Link].[DB}.[Owner].[TableName] Where SomeColumn = ''' + LocalVariable + ''' '

  Exec(@SQL)