Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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