Solved

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

Posted on 2011-03-18
10
1,344 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now