Monday, July 30, 2012 7:53 AM
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.
Monday, July 30, 2012 8:02 AM
What if you test the provider with a .udl file?
Monday, July 30, 2012 11:59 AM
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.
- If you have old Oracle clients, backup tnsnames.ora and sqlnet.ora files
- Uninstall and delete previous versions of oracle client. After reboot you can delete old directories.
- Run the Installation of the client. You have to install x32 client first and then follow the same procedure for x64 client.
- Choose Custom setup as installation type
- Setup properly the Oracle Base Path
- 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
- After installation, restore the TNS Names ORA.
- 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)
- Modify the system registry settings as follow and reboot.
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:
- Start SSMS
- Open Server Objects/Linked Servers/Providers node in the Object Explorer
- Right-click the OraOLEDB.Oracle provider and chose Properties
- 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.
Tuesday, July 31, 2012 6:33 AM
Thanks for the reply. i will try