How to set up and troubleshoot a linked server to an Oracle database in SQL Server (280106)

SUMMARY

This step-by-step article describes how to set up a linked server from a computer that is running Microsoft SQL Server to an Oracle database and also provides basic troubleshooting steps for common errors you may experience when you set up a linked server to Oracle.

Steps to set up a linked server to Oracle

1. You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.
2. Install the driver you want on the computer that is running SQL Server. Microsoft only supports Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle. If you use a third-party provider or a third-party driver to connect to Oracle, you must contact the respective vendor for any problems that you may experience by using their provider or driver.
3. If you use Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle, consider the following:
   • Both the OLE DB provider and the ODBC driver that are included with Microsoft Data Access Components (MDAC) require SQL*Net 2.3.x, or a later version. You must install the Oracle 7.3.x client software, or a later version, on the client computer. The client computer is the computer that is running SQL Server.
   • Make sure that you have MDAC 2.5, or a later version, installed on the computer that is running SQL Server. With MDAC 2.1, or with an earlier version, you cannot connect to databases that use Oracle 8.x or a later version.
   • To enable MDAC 2.5, or later versions, to work with Oracle client software, the registry must be modified on the client computer that is running SQL Server as indicated in the following table.

                Microsoft Windows NT,
     Oracle     Microsoft Windows 95,
     Client     Windows 98, and Windows 98 SE    Microsoft Windows 2000 
     --------------------------------------------------------------------------
     
     7.x        [HKEY_LOCAL_MACHINE\SOFTWARE     [HKEY_LOCAL_MACHINE\SOFTWARE  
                \Microsoft\TransactionServer     Microsoft\MSDTC\MTxOCI]
                \Local Computer\My Computer]     "OracleXaLib"="xa73.dll"
                "OracleXaLib"="xa73.dll"         "OracleSqlLib"="SQLLib18.dll"
                "OracleSqlLib"="SQLLib18.dll"    "OracleOciLib"="ociw32.dll"
                "OracleOciLib"="ociw32.dll"   
     
     8.0        [HKEY_LOCAL_MACHINE\SOFTWARE     [HKEY_LOCAL_MACHINE\SOFTWARE
                \Microsoft\Transaction Server    \Microsoft\MSDTC\MTxOCI]
                \Local Computer\My Computer]     "OracleXaLib"="xa80.dll"
                "OracleXaLib"="xa80.dll"         "OracleSqlLib"="sqllib80.dll" 
                "OracleSqlLib"="sqllib80.dll"    "OracleOciLib"="oci.dll"
                "OracleOciLib"="oci.dll" 
     
     8.1        [HKEY_LOCAL_MACHINE\SOFTWARE     [HKEY_LOCAL_MACHINE\SOFTWARE
                \Microsoft\Transaction Server    \Microsoft\MSDTC\MTxOCI]
                \Local Computer\My Computer]     "OracleXaLib"="oraclient8.dll" 
                "OracleXaLib"="oraclient8.dll"   "OracleSqlLib"="orasql8.dll"  
                "OracleSqlLib"="orasql8.dll"     "OracleOciLib"="oci.dll"
                "OracleOciLib"="oci.dll"

4. Restart the computer that is running SQL Server after you install the Oracle client software.
5. On the computer that is running SQL Server, set up a linked server by using the following script.

   -- Adding linked server (from SQL Server Books Online):
   /* sp_addlinkedserver [@server =] 'server' 
       [, [@srvproduct =] 'product_name']
       [, [@provider =] 'provider_name'] 
       [, [@datasrc =] 'data_source'] 
       [, [@location =] 'location'] [, [@provstr =] 'provider_string'] 
       [, [@catalog =] 'catalog']
   */ 
   
   EXEC sp_addlinkedserver   'Ora817Link',  'Oracle',  'MSDAORA',  'oracle817'
   
   -- Adding linked server login:
   /* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
       [,[@useself =] 'useself']
       [,[@locallogin =] 'locallogin']
       [,[@rmtuser =] 'rmtuser']
       [,[@rmtpassword =] 'rmtpassword']
   */ 
   
   EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'
   
   -- Help on the linked server:
   EXEC sp_linkedservers
   EXEC sp_helpserver
   select * from sysservers

Note If you use Microsoft ODBC Driver for Oracle, you can use the @datasrc parameter to specify a DSN name. For a DSN-less connection, the provider string is supplied through the @provstr parameter. With Microsoft OLE DB Provider for Oracle, use the Oracle server alias that is configured in the TNSNames.Ora file for the @datasrc parameter. For more information, see the "sp_addlinkedserver" topic in SQL Server Books Online.

Common error messages and how to troubleshoot them

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

You can use either of the following two methods to retrieve extended information about any error that you experience when you execute a distributed query.

• Method 1
  In Query Analyzer, run the following code to turn on trace flag 7300.

  DBCC Traceon(7300)

• Method 2
  Capture the "OLEDB Errors" event that is located in the "Errors and Warnings" event category in SQL Profiler. The error message format is the following:
  Interface::Method failed with hex-error code.
  You can look up hex-error code in the Oledberr.h file that is included with the MDAC Software Development Kit (SDK).

The following is a list of ten common error messages that may occur, together with information about how to troubleshoot the error message.

Note If you are using SQL Server 2005, these error messages may be slightly different. However, the error IDs of these error messages are same as them in SQL Server 2000. Therefore, you can identify them by the error IDs.

Note For performance-related issues, search SQL Server Books Online for the "Optimizing Distributed Queries" topic.

• Message 1
  Error 7399: OLE DB provider '%ls' reported an error. %ls
  Turn on trace flag 7300 or use SQL Profiler to capture the "OLEDB Errors" event to retrieve extended OLEDB error information.
• Message 2a
  "ORA-12154: TNS:could not resolve service name"
  Message 2b
  "The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation"
  For more information about how to resolve Oracle connectivity issues, click the following article number to view the article in the Microsoft Knowledge Base:

  259959 Techniques to debug connectivity issues to an Oracle server using the ODBC driver and OLE DB provider

• Message 3
  Error 7302: Could not create an instance of OLE DB provider 'MSDAORA'
  Make sure that the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle. If the registration fails, reinstall Microsoft Data Access Components (MDAC). For more information about MDAC, visit the following Microsoft Developer Network (MSDN) Web site:

  http://msdn.microsoft.com/data/Default.aspx

  Note If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following methods.
  • Method 1
    Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName

  • Method 2
    Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.
• Message 4
  Error 7303: Could not initialize data source object of OLE DB provider 'MSDAORA'. [OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d].
  This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration.
• Message 5
  Error 7306: Could not open table ' %ls' from OLE DB provider 'MSDAORA'. The specified table does not exist. [OLE/DB provider returned message: Table does not exist.][OLE/DB provider returned message: ORA-00942: table or view does not exist] OLE DB error trace [OLE/DB Provider 'MSDAORA' IOpenRowset::OpenRowset returned 0x80040e37: The specified table does not exist.].
  Error 7312: Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
  Error 7313: Invalid schema or catalog specified for provider '%ls'.
  Err 7314: OLE DB provider '%ls' does not contain table '%ls'
  If you receive these error messages, a table may be missing in the Oracle schema or you may not have permissions on that table. Verify that the schema name has been typed by using uppercase. The alphabetical case of the table and of the columns should be as specified in the Oracle system tables.
  
  On the Oracle side, a table or a column that is created without double quotation marks is stored in uppercase. If the table or the column is enclosed in double quotation marks, the table or the column is stored as is.
  
  The following call shows if the table exists in the Oracle schema. This call also shows the exact table name.

  sp_tables_ex  @table_server=Ora817Link, @table_schema='your_schema_name'

  For more information about error message 7306, click the following article number to view the article in the Microsoft Knowledge Base:

  240340 SQL distributed query with Oracle causes "Could not open table" error

• Message 6
  Error 7413: Could not perform a Windows NT authenticated login because delegation is not available.
  Msg 18456, Level 14, State 1, Line 1 Login failed for user '\'.
  The following information is from SQL Server Books Online:

  This error message indicates that a distributed query is being attempted for a Microsoft Windows authenticated login without an explicit login mapping. In an operating-system environment in which security delegation is not supported, Windows NT authenticated logins need an explicit mapping to a remote login and password created using sp_addlinkedsrvlogin.

• Message 7
  Error 7354: OLE DB provider 'MSDAORA' supplied invalid metadata for column '%ls'. The data type is not supported.
  If you receive this error message, you may be experiencing the bug that is described in the following Microsoft Knowledge Base article:

  243027 FIX: Numeric column in Oracle causes error 7354

• Message 8
  Error 7356: OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
  If your linked server query uses an Oracle view, you may be experiencing the problem that is described in the following Microsoft Knowledge Base article:

  251238 Distributed queries return error 7356 with MSDAORA

• Message 9
  Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]
  Verify that the OCI versions are registered correctly as described earlier in this article.
  
  Note If the registry entries are all correct, the MtxOCI.dll file is loaded. If the MtxOCI.dll file is not loaded, you cannot perform distributed transactions against Oracle by using Microsoft OLE DB Provider for Oracle or by using Microsoft ODBC Driver for Oracle. If you are using a third-party provider and you receive Error 7391, verify that the OLE DB provider that you are using supports distributed transactions. If the OLE DB provider does support distributed transactions, verify that the Microsoft Distributed Transaction Coordinator (MSDTC) is running.
• Message 10
  Error 7392: Could not start a transaction for OLE DB provider 'MSDAORA'. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].
  The following information is from SQL Server Books Online:

  The OLE DB provider returned error 7392 because only one transaction can be active for this session. This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects of the data modification statement while continuing with the transaction.

  If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB provider. Therefore, execute SET XACT_ABORT ON before you execute data modification statements against remote tables in an implicit or explicit transaction. Do this in case the OLE DB provider that you are using does not support nested transactions.