CODEWITHSUNDEEP
sql-server-2012
Sumendra Pandey
Sumendra Pandey

Reputation: 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "PROD" reported an error.

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "PROD" reported an error. The provider reported an unexpected catastrophic failure. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "PROD".

Since I have disable the "Allow InProcess" and enable back again, still the issue remains same.

Upvotes: 0

Views: 5047

Answers (1)

Qindi Zhang
Qindi Zhang

Reputation: 13

I have found a solution posted by this blogger. Try it out!

This tool from Sysinternals/Mark Russinovich is the best, and my only regret that day was not launching it earlier instead of scouring Google and going insane. I’ve limited Procmon to just sqlservr.exe, as it’s the SQL Service itself that loads/handles the providers and not the ssms.exe. Also of note is that the sqlservr.exe is a 64bit process while the management studio is still just 32bit. As the server service is loading the provider, and the service process is 64bit, the provider must also be available in 64 bit format.

The ODAC112021Xcopy_x64.zip was installed to C:\Oracle. What Procmon showed me however is that sqlservr is attempting to find the oci.dll in any folder but his! (It iterates through the %Path% sysvariable). When it finally gives up on find the dll, the SQL Service is in a unstable shape and the only way to stop the service was to kill it via taskmgr/procexp. Clearly I can see that the “xcopy” deployment – while not giving me any error messages – it also did not set the PATH variable! And this is what this post is really about… adding C:\Oracle and C:\Oracle\Bin to the Path variable or maybe it’s about employing investigative tools earlier in the process instead of relying on your search engine skills.

sqlservr.exe can now find the relevant DLL’s. The OCI.DLL in the root and the OraOLEDB11.DLL in the Bin subfolder. At this point I could query the database! If you did my steps as above and you still get the same error, I strongly suggest using Procmon.exe as I have instead of jumping to the next search result.

Full post is here with more details.

Upvotes: 1

Related Questions