Solved

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

Posted on 2011-03-18
10
1,331 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

743 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

11 Experts available now in Live!

Get 1:1 Help Now