Steps to connect SQL Server to Oracle works but WHY ??
The following steps is what I have to do in order to get SQL Server to talk to an Oracle database.
These steps, for me, work 100% of the time. Any deviation from the following steps will result in failure.
Rebooting the machine didn't make any difference between each of the steps.
My question is why I have to install the client a second time?
All the documentation that I have found at Oracle, Microsoft, or elsewhere state that I only need the InstantClient.
=========================================
Some server I don't have access to
with Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit installed
Windows XP SP3 - 32bit
with Microsoft SQL Server 2005 SP3 installed
The following steps are done on the Windows XP machine.
1) install win32_11gR2_client
Select: InstantClient (174.0MB)
Path: C:\Oracle
2) install ODTwithODAC112021
Select: Oracle Data Access Components for Oracle Client
Oracle Base: C:\Oracle
Name: OraClient11g_home1
Path: C:\Oracle\product\11.2.0\client_1
3) Add entries for tnsnames.ora and sqlnet.ora
4) Ensure environmental variable ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_1
5) SQL Server Management Studio
Check "Allow Inprocess" for Oracle Provider OraOLEDB.Oracle
Restart SQL Server
Try to add link to Oracle - fails to connect
"The test connection to the linked server failed."
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB Provider "OraOLEDB.Oracle" for linked server "ORACLEDB".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEDB" returned message "".
(Microsoft SQL Server, Error: 7303)
6) install win32_11gR2_client
Select: Administrator (1.02GB)
Oracle Base: C:\Oracle
Software Location: C:\Oracle\product\11.2.0\client_1
7) Ensure environmental variable ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_1
8) Ensure tnsnames.ora and sqlnet.ora still exist and are unchanged.
9) SQL Server Management Studio
Restart SQL Server
Add link to Oracle - Successful
These steps, for me, work 100% of the time. Any deviation from the following steps will result in failure.
Rebooting the machine didn't make any difference between each of the steps.
My question is why I have to install the client a second time?
All the documentation that I have found at Oracle, Microsoft, or elsewhere state that I only need the InstantClient.
=========================================
Some server I don't have access to
with Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit installed
Windows XP SP3 - 32bit
with Microsoft SQL Server 2005 SP3 installed
The following steps are done on the Windows XP machine.
1) install win32_11gR2_client
Select: InstantClient (174.0MB)
Path: C:\Oracle
2) install ODTwithODAC112021
Select: Oracle Data Access Components for Oracle Client
Oracle Base: C:\Oracle
Name: OraClient11g_home1
Path: C:\Oracle\product\11.2.0\client_1
3) Add entries for tnsnames.ora and sqlnet.ora
4) Ensure environmental variable ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_1
5) SQL Server Management Studio
Check "Allow Inprocess" for Oracle Provider OraOLEDB.Oracle
Restart SQL Server
Try to add link to Oracle - fails to connect
"The test connection to the linked server failed."
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB Provider "OraOLEDB.Oracle" for linked server "ORACLEDB".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEDB" returned message "".
(Microsoft SQL Server, Error: 7303)
6) install win32_11gR2_client
Select: Administrator (1.02GB)
Oracle Base: C:\Oracle
Software Location: C:\Oracle\product\11.2.0\client_1
7) Ensure environmental variable ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_1
8) Ensure tnsnames.ora and sqlnet.ora still exist and are unchanged.
9) SQL Server Management Studio
Restart SQL Server
Add link to Oracle - Successful
