Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

As per standard procedure we've been using for years for previous versions of both Oracle and SQL Server, I have installed Oracle's latest ODAC package, which includes the Oracle OLE driver onto our new SQL Server 2008 R2 x64 nodes. I have done the recommended system reboot, but OraOLEDB.Oracle does not show in the Linked Servers\Providers node in SSMS. The only difference between this installation and previous SQL Server installations is that I am now using SQL Server x64 (on Windows 2008 R2). Should this make any difference?

Note that I am able to connect to Oracle servers using SQL*Plus from the SQL Server nodes directly. The only thing I am missing is that Provider. Anyone know what I am missing? There are many posts around the web, but there seems to be a lot of confusion and outdated links to Oracle's download page.

The ONLY thing I need to be able to do is create a linked server to Oracle, and run select queries against it. I don't need to do anything through Visual Studio.

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

After hours of research, I managed to cobble together some working instructions for Oracle 11g R2. As it turns out, you will likely need both the 32 and 64 bit clients installed to have things working in BIDS/Visual Studio/SSMS. I may have installed more Oracle components than I needed, but here's what worked for me:

  1. Download 32 and 64-bit clients from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (click "see all" to see different versions, otherwise, you'll be downloading the complete Oracle program. Your downloads should be about 600 meg each)
  2. Run 32-Bit install on the SQL Server. Select "Custom"
  3. For Oracle Base, enter "C:\Oracle"
  4. For "Software Location", change to C:\Oracle\product\11.2.0\client_32
  5. Choose the following components:
  6. SQL*Plus
  7. Oracle Call Interface (OCI)
  8. Oracle Net
  9. Oracle Services for Microsoft Transaction Server
  10. Oracle Administration Assistant for Windows
  11. Oracle Provider for OLE DB
  12. Oracle Data Provider for .NET
  13. Oracle Providers for ASP.NET
  14. Repeat above steps with the 64-bit installer. HOWEVER, change the "Software Location" to C:\Oracle\product\11.2.0\client_64
  15. I got a memory error at this point, but chose to ignore it since I know there is enough
  16. Ignore the error "OracleMTSRecoveryService already exists"

Now that the install is done, just need to tweak a few things.

  1. Make registry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
  2. OracleOciLib should be oci.dll
  3. OracleSqlLib should be orasql11.dll
  4. OracleXaLib should be oraclient11.dll
  5. Make same registry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
  6. Create or copy a new tnsnames.ora in C:\Oracle\product\11.2.0\client_32\network\admin
  7. Create or copy a new tnsnames.ora in C:\Oracle\product\11.2.0\client_64\network\admin
  8. Reboot!
  9. You should now see OraOLEDB.Oracle as a provider under Linked Servers\Providers in SSMS
  10. Right-click this provider, then Properties, then tick the box for "Allow Inprocess"
  11. You can now create linked server through the GUI or T-SQL

Good luck!

share|improve this answer
1  
I had the same problem (Sql Server 2008 R2 64x connecting to an Oracle 8), and solved it following your instructions. Fortunately, I just had to install the 64 bit client. Thanks! –  santiiiii Oct 14 '10 at 9:02
    
THANKS!!!! This has solved my problem too. –  natxo asenjo Nov 9 '10 at 13:44
    
I installed it without the SQL*Plus and Administration Assistant, and without the registry changes on a x64 machine and it worked. –  Gabriel Guimarães May 18 '11 at 12:47
add comment

Yes, the system architecture makes a lot of difference.

You need to install the x64 Oracle client software for Windows.

share|improve this answer
    
I can't seem to find x64 components for 11g on the Oracle site without downloading the entire x64 client which is 2GB. The ODAC only seems to go up to 10g. Is anyone else noticing this? I'll try the full client for now, but I've never had to do that before. Maybe they will be releasing ODAC 11g for x64 in the future. –  SomeGuy Aug 27 '10 at 14:02
    
The client is ~615 MB, the 2GB package is the full product; see here: oracle.com/technetwork/database/enterprise-edition/downloads/…. –  Massimo Aug 27 '10 at 15:00
2  
@SomeGuy actually there's ODAC for 11g, you couldn't find it because oracle website is a mess. –  Gabriel Guimarães May 18 '11 at 12:49
add comment

protected by Community May 10 '11 at 14:30

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.