0

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...
  • 1
    Did you install the 64 and 32 bit version of the oracle client, SSMS is 32 bit and the engine is 64 bit? Do you see the oracle provider in "odbc data sources"? – Spörri May 12 '15 at 9:24
  • 1
    Yes, I added the Oracle Driver Correctly, I see it in Both Linked Providers in SSMS, as well as in the ODBC/DSN Driver List. (We have made a test connection with ODBC, just to ensure that it is not a Network/Connectivity error. – user58700 May 12 '15 at 14:23
  • 1
    EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1 – Spörri May 12 '15 at 14:51
  • It looks like this did the trick! Instead of running your TSQL Statement, I just interpreted it and set the Global Properties of OraOLEDB.Oracle to "Allow Inprocess" Thanks! – user58700 May 12 '15 at 15:15
2

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

  • I did all of this, except for Checking the "Allow Inprocess" Checkbox. Solved. – user58700 May 12 '15 at 16:00
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.