Oracle ODBC doesn't show up in SQL Server 2008 Enterprise after Oracle Client 11gR2 install ?

Hi Everyone,

I'm having difficulties in creating a connection from my SQL Server 2008 Enterprise SP2 x64 into the Oracle database 10g even though I have already install the Oracle Client 11g R2 ?

I've followed this article from steps URL:
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

Open in new window


plus added: TNS_ADMIN into the Server variables which point into: C:\Oracle\product\11.2.0\client_1\network\admin

what is working:
1. TNSNAMES.ORA has been copied successfully from the other Developer wworkstation
2. i can TNSPING into the DB instance
3. i can connect to the database using SQLplus and perform any SQL commands
4. i can create the DSN ONLY when using "C:\Windows\SysWOW64\odbcad32.exe" the normal odbcad32 doesn't show my DSN that I have just created ?
5. the DSN created from the above works fine from the test connection.

my goal: To be able to select the Oracle connection in the Linked server object but still no effect after I restart the server

Any idea please in resolving this problem would be greatly appreciated.

Thanks.
LVL 1
jjozAsked:
Who is Participating?
 
Daniel_PLDB Expert/ArchitectCommented:
Please install Oracle client back again if uninstalled.
Check your TNSNAMES.ORA file, test connection with tnsping. When those are correct check listener service on the remote server.

http://www.dba-oracle.com/t_ora_12541_tns_no_listener.htm
0
 
Daniel_PLDB Expert/ArchitectCommented:
In which point in this article you fail?
0
 
jjozAuthor Commented:
step number 5 Configure provider in SQL Server as I couldn't find Oracle component ?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Daniel_PLDB Expert/ArchitectCommented:
I've just set up 32 bit version of Oracle Client and Oracle Data Access Components. I didn't focus on connecting do Oracle database but Oracle provider is available in SQL Server right after restart.
I suppose you didn't installed Oracle Data Access Components correctly, I installed ODAC package and during install I unchecked .NET options.
I used 32 bit version, you probably need 64 bit version:
http://download.oracle.com/otn/other/ole-oo4o/ODAC112021Xcopy_x64.zip
Or this if you want to stay with 10g:
http://download.oracle.com/otn/other/ole-oo4o/ODAC10203x64.zip
0
 
jjozAuthor Commented:
Daniel, thanks for your reply.

I have installed the ODAC from my (Admin privillege cmd prompt) by typing the install.bat all C:\Oracle odac it went OK, no prompt or whatsoever, BUT...

I cannot TNSPING and there is no SQLplus (no problem) the pr real problem is that in ODBCAD32 and the 64 bit ODBCAD i cannot see Oracle at all ? even after restarting the whole DB server ?
0
 
jjozAuthor Commented:
I have also uninstall my Oracle 11gR2 client on the server from the cmd prompt and then followed by the OUI manual uninstall.
0
 
jjozAuthor Commented:
ok, now here's the latest update from my SQL Server management console
I can now follow the steps from the above link, but when I click on OK, it failed.

yes 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 ?
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
------------------------------

Open in new window

0
 
jjozAuthor Commented:
ah yes, I've no installed the Oracle Client 11gR2 on top of my ODAC installation and it works now.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,
Is this issue resolved? If so, can you close this by assigning points?
0
 
jjozAuthor Commented:
thanks man !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.