Cannot create a linked server to Oracle.

Applies to: SQL Server 2008 R2 Enterprise Edition, Windows Server 2008 R2 Enterprise Edition.
 

Problem Description.
 

We downloaded and installed the Oracle 11g client 11gR1 (win64_11gR1_client or 11.1) to create a linked server between a SQL Server 2008 R2 Enterprise Edition instance and a Oracle 11g instance. We then created the linked server successfully but when we tried to expand the Tables container on the linked server to show the tables available on the remote Oracle instance, we received the following error:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot create an instance of OLE DB Provider "OraOLEDB.Oracle" for linked server "RH6I".
(Microsoft SQL Server, Error: 7302)






We enabled the option "Allow in process" on the properties of the OraOLEDB.oracle provider, but we the error above still persisted.

Solution.


We found that a new release of the Oracle client was available (win64_11gR2_client or 11.2). Once we installed the new version and enabled the option "Allow in process", the linked server worked correctly. We were able to expand the Tables container on the linked server.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.