Installing Oracle drivers on a SQL Server box presents it's own set of challenges, but sometimes it must be done. I recently had to do just that. Unfortunately mixing Oracle with SQL Server is like mixing oil and water; I think it goes without saying that I had some issues.
SQL Server: 2012
Oracle: 11g
The installation of Oracle ODAC x64 and x86 went smoothly. When I tried to create the linked server in Management Studio I kept getting errors. The error was very obtuse and just said that SSMS couldn't create the linked server.
Testing the Oracle drivers via .UDL
I created a .udl to test the connection which worked fine. The issue didn't appear to be the drivers.
Allow Inprocess
After digging around online I found several blog posts about allowing inprocess for the provider. It's a simple check box that needed to be checked.
SSMS >> Server Objects >> Linked Servers >> Providers
Right-click OraOLEDB.Oracle and select Properties. Enable Allow inprocess.
The problem persisted.
I un-installed the drivers and re-installed them and nothing seemed to work. I double-checked the .ora files and they seemed to be fine.
(by the way, the .ora files you use must be installed into the driver installation directory under the ...\Network\Admin folder. For example mine is the following directory: C:\OracleODAC64\product\11.2.0\client_1\Network\Admin)
Still not satisfied I dug deeper on the Internet and discovered a forum where some folks had the exact same issue. One poster, deep within the thread, said that the allow inprocess check box was checked but it wasn't until he unchecked the check box did things work for him.
I tried it.
And it worked....!
I don't know why, but I was finally able to create my linked server without issue.
No comments:
Post a Comment