Executing command on Oracle via linked server

Question

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

Answer 1

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

Answer 2

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