Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    6

    Post Unanswered: how to execute openquery to oracle

    Hi, I am having the problem on execute store procedure at oracle from Ms SQL server.
    In oracle, i have a sp_test1 for pass in a string and will run some functions. It work fine at oracle, but how can i use sql server to execute that store procedure(sp_test1)?

    the below is the way of syntax I used in oracle
    begin
    sp_test1('073549');
    end;


    the below is the way of syntax I try in sql server to execute that oracle store procedure but it unsuccessful.

    select * from openquery (sbnprod, 'sp_test1 (''073549'')')

    -: Assume sbnprod is the link connection to oracle and it is success connect.

    Thanks

    Regards

    Billy

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    what error are you getting back?

    Also, do you need to prefix the stored procedure call with db & owner as one would in SQL Server?

    Here is an example of SQL Server to SQL Server.

    Code:
    --------------------------------------------------------------------------------------------------
    select * from OPENQUERY(HGW2DB18, 'dba.dbo.GetProcUsage')
    --------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    6
    My problem is when using link server to connect oracle, if normal select statement is ok, but how to call the store procedure in oracle from open query? The store procedure is not return any result, just maybe do some update information only. Is there any solution for that??

    my statement is

    select * from openquery (sbnprod, 'sp_test1 (''073549'')')

    Error Message
    Could not process object 'sp_standardpo_test1 ('073549')'. The OLE DB provider 'MSDAORA' indicates that the object has no columns.


    in oracle way is
    begin
    sp_test1 ('073549');
    end;

    Thanks.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    A few thoughts....

    OPENQUERY won't work because OPENQUERY expects a results set to process, you probably figured that one out by now. Normaly The OPENQUERY, OPENROWSET and OPENDATASOURCE are used when you want to join directly to a table on a remote server. These functions limit the amount of data flowing back and forth.

    If I needed to run a stored proc on another SQL server, regardless of what the stored proc did, I would call the stored proc using a four part nameing convention: <SERVER ALIAS>.<DATABASE NAME>.<OWNER>.<STORED PROCEDURE NAME>. I think I would play dumb and try that with Oracle. I am NOT an Oracle kind of guy but I am sure that a similar nameing convention exists in Oracle.

    Look up "accessing external data" in the SQL Books On Line. there is a paragraph towards the bottom about accessing stored procedures on linked servers. Maybe this will help.
    Last edited by Paul Young; 06-27-02 at 08:02.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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