2 Replies Latest reply: Oct 9, 2012 8:55 PM by 967297 RSS

    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 - 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.
      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