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
Best Answer
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:
after that drop the TNSNames into the C:\Oracle\Client\product\11.2.0\client_1\network\admin directory