Cannot create a linked server to Oracle.
Applies to: SQL Server 2008 R2 Enterprise Edition, Windows Server 2008 R2
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)
An exception occurred while executing a Transact-SQL statement or batch.
Cannot create an instance of OLE DB Provider "OraOLEDB.Oracle" for linked server
(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.
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.