Passing parameters to a linked server stored proc

  • chrisegan

    Old Hand

    Points: 325

    I have created a stored procedure on SQLServer 2000 which collects data from a linked Sybase server to be placed in a table on the SQLServer. Due to issues with the sybase ODBC Driver provided with Sybase, Inserting into a temp table from a Remote Procedure Call directly will not work, i.e.:

    INSERT INTO STAGING_TABLE

    EXEC LINKEDSERVER...sp_SybaseData '01/01/2001', '06/30/2001'

    Produces an 'Optional feature not implemented' error as the driver does not allow inserts or update in the transaction.

    (Executing the EXEC portion only without the INSERT line works fine.)

    However, when I use the OPENQUERY command I can perform the insert with no problems. The issue here is when I call the sp with no parameters it works fine, i.e.:

    INSERT INTO STAGING_TABLE

    SELECT * FROM OPENQUERY(LINKEDSERVER,'EXEC sp_SybaseData')

    But when I attempt to pass the dates into the SP using OPENQUERY, i.e:

    INSERT INTO STAGING_TABLE

    SELECT * FROM OPENQUERY(LINKEDSERVER,'EXEC sp_SybaseData ''01/01/2001'', ''06/30/2001'' ')

    I recieve the following error:

    Server: Msg 7357, Level 16, State 2, Line 1

    Could not process object 'EXEC sp_SybaseData '01/01/2001', '06/30/2001' '. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

    Thanks for your time, and any suggestions on how to send quoted parameters inside of an OPENQUERY would be greatly appreciated.

    Chris Egan

    Edited by - chrisegan on 08/01/2001 09:48:32 AM

  • Andy Warren

    SSC Guru

    Points: 119676

    Hey Chris,

    Looks interesting. I used linked servers pretty often, but mostly for just direct table access and not using procs. Possibly you're hitting a limitation of the data provider.

    One work around would be to modify your process a bit so that instead of passing parameters, you insert them into a table on the remote server (or registry entry, ini file, whatever) so that you can run the proc with no parameters, or even have it generate a table that you can do just a plain select from. Not a good answer, but maybe will help while we look for the good answer.

    Andy

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

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