'Connect Oracle to SQL Server

I am trying to connect Oracle 9i database using SQL Management studio 2008.

I tried this but it doesn't work

EXEC sp_addlinkedserver 'OracleLinkedServer','Oracle','OraOLEDB.Oracle','TestDB';

EXEC sp_addlinkedsrvlogin 'OracleLinkedServer','false','SA','TestUsername','TestPassword';

I am able to create the linked server and it test correctly,

But when I execute this statement:

SELECT sysdate FROM OracleLinkedServer...dual;

...it gives an error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "OracleLinkedServer" reported an error. Provider caused a server fault in an external process.

Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "OracleLinkedServer". The provider supports the interface, but returns a failure code when it is used.

sql-serveroraclelinked-server


Solution 1:[1]

You should UNCHECKED the Allow inprocess in OraOLEDB.Oracle provider under the Linked Servers node.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 vaheeds

Related Questions