Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: 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
    bhavya

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The 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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    May 2004
    Posts
    7
    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....

  4. #4
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    Try openquery

    Refer this link
    http://www.dbforums.com/t440123.html

  5. #5
    Join Date
    May 2004
    Posts
    7
    how could i call remote stored procedure using openquery.....? could u plz write down the statement for doing so?

  6. #6
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36

    updated information

    Sorry I brought the wrong information. Please check out this link instead

    http://www.winnetmag.com/SQLServer/A...268/22268.html

    Maybe this is the link that Enigma was talking about!

  7. #7
    Join Date
    May 2004
    Posts
    7
    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?

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •