I've downloaded and install the ODAC for connecting oracle DB into my SQL Server 2008 using http://download.oracle.com/otn/other/ole-oo4o/ODAC112021Xcopy_x64.zip

The OleDB data provider for Oracle is now available in the SQL Server, but doesn't seems to be usable for now, Shall I install oracle 11gR2 client 64 bit on top of this ODAC ?

the following is the result of the right clicking the connection to the oracle server that I just created to test the server connection.

TITLE: Microsoft SQL Server Management Studio
The test connection to the linked server failed.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "DW-DB_PROD".
OLE DB provider "OraOLEDB.Oracle" for linked server "DW-DB_PROD" returned message "ORA-12541: TNS:no listener". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.4000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476


DW-DB_PROD is the DB instance name that is available and working from TNSPING command and I can get in from the SQLPLUS.

Any kind of help would be greatly appreciated.



Installing ODAC 11g 64 bit first in the SQL Server and then editing the TNS names solves the problem.


and set the following Environment variables:


after that drop the TNSNames into the C:\Oracle\Client\product\11.2.0\client_1\network\admin directory

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.