Solved

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

Posted on 2011-03-18
10
1,375 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:jjoz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35164229
In which point in this article you fail?
0
 
LVL 1

Author Comment

by:jjoz
ID: 35165974
step number 5 Configure provider in SQL Server as I couldn't find Oracle component ?
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 500 total points
ID: 35171465
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:jjoz
ID: 35177609
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
 
LVL 1

Author Comment

by:jjoz
ID: 35177610
I have also uninstall my Oracle 11gR2 client on the server from the cmd prompt and then followed by the OUI manual uninstall.
0
 
LVL 1

Author Comment

by:jjoz
ID: 35177941
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
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35178394
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
 
LVL 1

Assisted Solution

by:jjoz
jjoz earned 0 total points
ID: 35178410
ah yes, I've no installed the Oracle Client 11gR2 on top of my ODAC installation and it works now.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35230310
Hi,
Is this issue resolved? If so, can you close this by assigning points?
0
 
LVL 1

Author Closing Comment

by:jjoz
ID: 35304185
thanks man !
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question