Unable to access Oracle DB from SQL Server with ODAC?

Question

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.
------------------------------
ADDITIONAL INFORMATION:
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

------------------------------
BUTTONS:
OK
------------------------------

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.

Thanks

Answer 1

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

ODAC112021Xcopy_x64.zip

and set the following Environment variables:

ORACLE_HOME=C:\Oracle\Client\product\11.2.0\client_1
Path=C:\Oracle\Client\product\11.2.0\client_1\bin\;C:\Oracle;C:\Oracle\bin
TNS_ADMIN=

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