OLE DB Driver sql 2008r2 to Oracle failing

All we need is an easy explanation of the problem, so here it is.

I have a SQL Server R2 Installation running on Windows Server 2008 R2.
I have installed Oracle Instant Client, registered the OraOLEDB.Oracle driver(orasql12.dll), and set the PATH in environment variables to the BIN folder of the Oracle installation.

I can see the OraOLEDB.Oracle provider in SQL Management Studio, however, when I try to connect to the remote datababase using OpenRowSet (see code below), I get the following error:

The OLE DB provider "[OraOLEDB.Oracle]" has not been registered.

I know that I have successfully registered this driver with regsvr32, and I still cant connect? What am I doing wrong?

The code I am using is as follows:

SELECT *
FROM   OPENROWSET('[OraOLEDB.Oracle]','[TNS]';'[UserName]';'[Password]','SELECT DataField, TYPE'); etc...

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Install the Oracle Instant client – both the 32 and 64 bit versions find orasql12.dll and register it with regsvr32 orasql12.dll and c:\windows\syswow64\regsvr32.exe orasql12.dll for the 32 bit version. and add the oracle bin directory the the path environment variable

Make sure that you can see the oracle driver in ODBC both 32bit and 64bit and under data providers in the management studio. Check allow “Allow Inprocess” in the driver properties or run EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

Method 2

Checking “Allow Inprocess” in the Properties of the Driver “OraOLEDB.Oracle” in SSMS allowed the driver to work correctly with our OpenRowSet Query against the Remote Oracle Server.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply