|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Calling an Oracle Stored Procedure from a Sql Server 2000 Stored Procedure using a Linked ServerHi,
I'm trying to call an Oracle stored procedure from Sql server and I have this error message Could not execute procedure 'INTEGRE_INTERNET' on remote server 'ORA_DEV1'. [OLE/DB provider returned message: One or more errors occurred during processing of command.] [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] My linked server seems to be ok, I can run a "Select " query from the query analyser to an Oracle table but I can't run an oracle stored procedure... Do you have any idea ? thanks richard I am very sorry to hear about this Richard!
I had the same issue... The way I got around this was to create a trigger on an input table in Oracle to fire off the SP. the table should hold all variables the SP needs and the trigger should pass those values. on the Oracle side you need to have the SP load a results table and you need to use openquery to retrieve the data from the results table. Real PITA but it works well. I can't help you with the Oracle side but I can help you with the SQL piece. HTH, Joe I found another way to get this result.
I created an Oracle function that call the Oracle SP an return "ok". I call the function with : SELECT * FROM OPENQUERY(ORA_LINEKED, 'SELECT DATABASE.FUNCTION FROM DUAL') the result is ok. Show quote "jaylou" <jay***@discussions.microsoft.com> wrote in message news:929A69F2-A39C-45D6-BCAA-450F785A05D3@microsoft.com... >I am very sorry to hear about this Richard! > I had the same issue... > > The way I got around this was to create a trigger on an input table in > Oracle to fire off the SP. the table should hold all variables the SP > needs > and the trigger should pass those values. on the Oracle side you need to > have the SP load a results table and you need to use openquery to retrieve > the data from the results table. > > Real PITA but it works well. > > I can't help you with the Oracle side but I can help you with the SQL > piece. > > HTH, > Joe >  |
|||||||||||||||||||||||
Other interesting topics