?
Solved

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

Posted on 2011-03-18
10
Medium Priority
?
1,394 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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