SQL 2008 R2 (64 bit) Linked Server configuration with Oracle

Question

Hello

Running SQL 2008 R2 64 bit in Windows 2008 R2 standered edition server. We need to configure Linked server with Oracle.

Oracle Database 10G express edition and Oracle ODAC 64 bit is installed in SQL server. While connecting oracle database below error is showing. And there is no MSDORA provider in providers list (only OraOLEDB).

The linked server has been created but failed a connection test. cannot create an instance of LEDB provide "OraOLEDB.oracle" for linked server ( Microsoft SQL server, Error:7302)

Do i need to do any work around to over come this?

Appreciate your help.

Answer 1

What if you test the provider with a .udl file?

http://blogs.msdn.com/b/farukcelik/archive/2007/12/31/basics-first-udl-test.aspx

It's normal there's isn't a MSDORA provider listed, as it's not available in 64-bit.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 2

To successfully setup oracle Client to work with x64 version of SQL server, fllow below steps: This works with 11g client, which you can use too connect to 10g oracla as well.

1. If you have old Oracle clients, backup tnsnames.ora and sqlnet.ora files
2. Uninstall and delete previous versions of oracle client. After reboot you can delete old directories.
3. Run the Installation of the client. You have to install x32 client first and then follow the same procedure for x64 client.
4. Choose Custom setup as installation type
5. Setup properly the Oracle Base Path
6. YOu have to select "Oracle Windows Interfaces 111.x.x components for OLEDB" in Available Product Components step. Additionally select Oracle NET 11.x and other
7. After installation, restore the TNS Names ORA.
8. Do the same for the x64. Oracle Base Path should be the same as for x32, but the final path has to be different: (eg. C:/Oracle/product/11.1/client_32; C:/Oracle/product/11.1/client_64)
9. Modify the system registry settings as follow and reboot.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI    
and 
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI 
a. OracleOciLib = oci.dll 
b. OracleSqlLib = orasql11.dll   (old: SQLLib80.dll) 
c. OracleXaLib = oraclient11.dll  (old: xa80.dll) 

After installation to allow browsing object of Oracle Linke Servers in SSMS, follow this:

1. Start SSMS
2. Open Server Objects/Linked Servers/Providers node in the Object Explorer
3. Right-click the OraOLEDB.Oracle provider and chose Properties
4. In the properties dialog check the Allow inprocess

Both x32 and x64 drivers are necessary if you wish to use the Client applications (SSMS, BIDS) etc as those are x32 processes. And x64 server needs an x64 oracle driver to process queries using the Linked Server.

Answer 3

Thanks for the reply. i will try