SQL Server – OLE DB Driver Failing from SQL 2008r2 to Oracle

oraclesql server

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...

Best Answer

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

Related Question