August 1, 2001 at 9:46 am
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
August 1, 2001 at 10:24 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply