Problems with a linked server from a SQL Server 2008 R2 to Oracle 11.2g

Question

Hi,
I've 64-bit SQL Server 2008 R2 (SP1) on 64-bit Windows Server 2008 R2 (SP1) and Oracle 11.2g. I installed the Runtime option of 64-bit Oracle 11g Client Release 2, edited %ORACLE_HOME%\Network\Admin\TNSNAMES.ora and restarted the SQL Server instance and Windows server.

When I created a linked server and tested the connection, I got "The OLE DB provider 'OraOLEDB.Oracle' has not been registered. (Microsoft SQL Server, Error: 7403)". I ran regsvr32 OraOLEDB11.dll and restarted the Windows server but got the same error. The PATH includes %ORACLE_HOME%\bin directory. The 'Authenticated Users' have read & execute permissions to %ORACLE_HOME% directory and its subdirectories and files. Any idea how to fix this?

Answer 1

Hi

I thought..

The OLE DB provider is not registered properly, Can you pelase check twice please

---

subu

Answer 2

Well,

I've reinstalled this 64-bit Oracle 11g Client Release 2 several times, without any effects. The major problems is that now linked servers and SSIS don't work with Oracle.

Answer 3

Hi nurmiju,

Make sure that you have installed OLE DB provider successfully.

Process Creating Oracle Linked Server on a 64-bit SQL Instance:

 •Install Oracle Release 2 64-bit client software (available from Oracle website)
 •Install Oracle Release 2 64-bit ODAC software (available from Oracle website)
 •Restart SQL services
 •Configure OraOLEDB. Oracle provider
 •Create linked server
 •Add remote logins for linked server

For more information, please check: http://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/.

Refer to Oracle Provider for OLE DB: http://msdn.microsoft.com/en-us/library/ms190618(v=sql.105).aspx.
 
Please see: How to set up and troubleshoot a linked server to an Oracle database in SQL Server: http://support.microsoft.com/kb/280106.

 

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 4

Ok,

I reinstalled Oracle Client 11.2.0.1.0 (win64_11gR2_client.zip) and Oracle Data Access Components for Oracle Client 11.2.0.3.0 (ODAC112030_x64.zip) and restarted the SQL Server instance. However, OraOLEDB.Oracle is still missing from the list of Providers under Linked Servers.

Answer 5

Hi,

Are you able to see this provide while creating a ODBC connection at Windows level (User DSN) and is it working?

---

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 6

Yes,

OBDC connection to Oracle 11.2g works well.

Answer 7

Now a linked server to Oracle 11.2g works. I reinstalled only Oracle Data Access Components for Oracle Client 11.2.0.3.0 and performed step 8 presented in http://thiruna.blog.com/2010/10/28/making-linked-server-connection-between-sql-server-64-bit-oracle-32-bit/.

Answer 8

Has anyone got an oracle linked server working from a SQL Server 2012 box. I cant for the life of me get sql server to see the provider? I even tried the STEP 8 from above.