Post reply

Using Linked Server Dynamic Parameter

  • 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?

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply