Using Linked Server Dynamic Parameter

• marks_262

  SSCommitted

  Points: 1716

  More actions

  October 22, 2014 at 10:02 am

  #300383

  For a linked server provider I have enabled the Dynamic parameter option.

  However I have tried a number of different combinations (example below) in the openquery function to include this which all error.

  SELECT *

  FROM OPENQUERY( ALinkedServer,

  'SELECT *

  FROM 'MyTable

  WHERE Field1 = ? ,''A'' ')

  )

  How can the dynamic parameter be used within an openquery statement?

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 22, 2014 at 2:55 pm

  #1754195

  Unfortunately, OPENQUERY doesn't support dynamic parameters.

  However, you could change your query to use EXEC...AT:

  EXEC('

  SELECT *

  FROM MyTable

  WHERE Field1 = ?' , 'A'

  ') AT ALinkedServer

  You may get error "Server 'ALinkedServer' is not configured for RPC."

  In that case you need to enable RPC:

  exec sp_serveroption @server='ALinkedServer' , @optname='rpc', @optvalue='true'

  exec sp_serveroption @server='ALinkedServer' , @optname='rpc out', @optvalue='true'

  -- Gianluca Sartori