Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Steps to connect SQL Server to Oracle works but WHY ??

888236Sep 21 2011 — edited Oct 9 2012
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 6 2012
Added on Sep 21 2011
#connection, #oracle-provider-for-ole-db, #sql, #windows-and-net
2 comments
28,960 views