×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Invoking Oracle stored procedures using SQL Server stored procedures

Invoking Oracle stored procedures using SQL Server stored procedures

Invoking Oracle stored procedures using SQL Server stored procedures

(OP)
I have a stored procedure that needs to execute a stored procedure on a linked Oracle 8i server.  I can query the tables and views in the linked server but when I try to execute the stored procedure as follows:

EXEC AURA..TEST_PKG.TEST_PROC

I get this error:

Could not execute procedure 'TEST_PROC' on remote server 'AURA'.
[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.]

The linked server has RPC and RPC OUT set in the server properties if this helps.

RE: Invoking Oracle stored procedures using SQL Server stored procedures

It's possible that your SQL Server stored procedure is using a syntax that is not recognized by ORACLE 8i's SQL. You might want to look at the SQL Server SP and compare all the syntax to Oracle's SQL syntax.

-SQLBill

RE: Invoking Oracle stored procedures using SQL Server stored procedures

(OP)
I would think that to be true as well except that the procedure does nothing, requires no input, and delivers no output.  I get this issue with all oracle stored procedures no matter what there signature is.  The only thing I can think is that maybe the translation from T-SQL to PL\SQL is what is inducing the error or the OLE DB for Oracle data provider has issues with the execution of remote procedures.  But I am not sure.  Any other ideas or possible solutions?

RE: Invoking Oracle stored procedures using SQL Server stored procedures


Try this. I've not used linked Oracle servers so I'm not sure if it will work. Though it might be worth trying.

Select *
From OpenQuery(AURA, 'EXEC TEST_PKG.TEST_PROC')

If you want to get the best answer for your question read FAQ183-874 and Thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page: http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

RE: Invoking Oracle stored procedures using SQL Server stored procedures

Am not sure it can be done. Not according to SQLMAG.COM anyway...

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=22268

The link above suggests to create a trigger on the oracle table that does nothing but call the oracle stored procedure...

Hope that helps ..

RE: Invoking Oracle stored procedures using SQL Server stored procedures


Apparently, it can be done using OpenQuery. However, the OpenQuery statement expects a result set. You can add a dummy result set as explained by Umachandar Jayac at the following links.

http://groups.google.com/groups?selm=e5IOrnU3AHA.1744%40tkmsftngp05

http://dbforums.com/archives/t296950.html

If you want to get the best answer for your question read FAQ183-874 and Thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page: http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

RE: Invoking Oracle stored procedures using SQL Server stored procedures


Thanks Terry ...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close