none
Executing command on Oracle via linked server

    Dotaz

  • Hi,

    I have two problems:

    1) I need executing stored procedure on Oracle 11g via linked server from MSSQL Express 2012, I use ODBC.

    I try:

    execute ('exec TEST_SCH.MY_PROC;') at ORA_1;

    Send error:

    OLE DB provider "MSDASQL" for linked server "ORA_1" returned message "[Oracle][ODBC]Syntax error or access violation.".
    Msg 7215, Level 17, State 1, Line 3
    Could not execute statement on remote server 'ORA_1'.

    2) Can I write select on MSSQL without OPENQUERY?

    this works: SELECT * FROM OPENQUERY(ORA_1, 'SELECT atrb FROM TEST_SCH.my_table;')

    but, I need the same as the table is on MSSQL - SELECT atrb FROM ORA_1..TEST_SCH.my_table;

    When I call that, I receive:

    Msg 7318, Level 16, State 1, Line 15
    The OLE DB provider "MSDASQL" for linked server "ORA_1" returned an invalid column definition for table ""TEST_SCH"."MY_TABLE"".

    Thanks




    27. října 2015 8:42

Všechny reakce

  • ad 1) try

    EXEC ORA_1.db_name.TEST_SCH.MY_PROC

    ad 2) try

    SELECT atrb FROM ORA_1.db_name.TEST_SCH.my_table;

    If you are using four-part name, you should not ommit database name.


    Robert Haken, Microsoft MVP ASP.NET/IIS, HAVIT, s.r.o., www.havit.cz, http://knowledge-base.havit.cz

    5. listopadu 2015 19:07
    Moderátor
  • Hello Robert,

    it still doesn`t works.

    I use four-part name. Linked server and Oracle instance have the same name.

    1)

    EXEC ORA_1.ORA_1.SCH_MAIN.MY_PROC;

    Send the same error:

    OLE DB provider "MSDASQL" for linked server "ORA_1" returned message "[Oracle][ODBC]Syntax error or access violation.".
    Msg 7215, Level 17, State 1, Line 3
    Could not execute statement on remote server 'ORA_1'.

    2)

    I try:

    select * from ORA_1.ORA_1.SCH_MAIN.TEST_TAB;

    Error:

    Msg 7312, Level 16, State 1, Line 2
    Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "ORA_1". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

    May be something on provider? I have checked only "Allow inprocess".

    Or I need change any parameter on Oracle?

    Thanks

    6. listopadu 2015 7:46