Running SQL Server 2012 (local), attempting to link to an Oracle 11 database (remote).
-
Installed Oracle 11g client 64 bit software
-
After much angst and attempts at different things, I was able to get it to work by setting "Allow inprocess" to true in the Providers Properties for OraOLEDB.Oracle and in the linked server properties,
a) Provider "Oracle Provider for OLE DB"
b) Product Name "Oracle"
c) Data Source "host:port/service name" -
This worked fine, until someone who faces my wrath the next time I see them, rebooted the local DB server.
-
Now, when I attempt to "Test Connection", I get two errors:
a) "The OLE DB provider "OraOLEDB.Oracle" for linked server "foobar" reported an error. The provider reported an unexpected catastrophic failure."
b) "Cannot initialize the data source of OLE DB provider "OraOLEDB.Oracle" for linked server "foobar". (Microsoft SQL Server , Error:7399)"
Three things to keep in mind: 1) This was working before the reboot (I have no idea whether the reboot has anything to do with the problem), 2) I am able to connect to the remote database using Oracle SQL Developer on the same server that my SQL Server instance is on, as well as the 64-bit ODBC Administrator, and 3) when I uncheck "Allow Inprocess" in the Provider Properties for the OraOLEDB.Oracle provider and try to test the connection, I only get the second error (not the unexpected catastrophic failure error).
Thank you for your help. This is driving me nuts.