Thursday, March 22, 2012

calling remote procedure on oracle linked servre

I have linked Oracle Server as a linked server on my SQL server.

Now i want to execute the stored procedure on oracle server which has one input parameter and one output parameter.

i am using the following sql statements in SQL Server Query analyzer to execute that procedure

declare @.sal float
exec [GENEVA].testDB.dbo.EmployeeOutPrm
'1',
@.sal output

but getting the following error:

Server: Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'EmployeeOutPrm' on remote server 'GENEVA'.
[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.]

COULD ANYBODY HELP ME TO RESOLVE THE SAME

many thanks in advance
bhavyaThe only way i have heard of anyone do that was a roundabout way that worked something like this (I forget the link but it was some SQL Server site ... )

create a dummy table in Oracle

write a trigger on update of the table which calls the proc ...

insert a row into the dummy table

when ever you need to call the proc perform an update on the dummy table in oracle|||thanks for your reply...but i need to do it this way only...i can not write any trigger on the dummy table because the remote oracle server doesn't allow me to do this...|||Try openquery

Refer this link
http://www.dbforums.com/t440123.html|||how could i call remote stored procedure using openquery....? could u plz write down the statement for doing so?|||Sorry I brought the wrong information. Please check out this link instead

http://www.winnetmag.com/SQLServer/Article/ArticleID/22268/22268.html

Maybe this is the link that Enigma was talking about!|||thanx for the reply....could u plz tell me whether is it possible or not to call remote stored procedure using CALL (odbc API) API?|||Well ... thats not exactly the link I was talking about ... but that's almost the same ...
I have faced this problem before ... searched around a lot ... and found this to be the only solution posted on the net ... if you find otherwise ... do inform me about it by posting it here ...

No comments:

Post a Comment