How to configure Oracle linked server in SQL Server and troubleshoot

View the products used in this article

Important: This article contains information about how to modify the registry. Before modifying the registry, you must back up the registry and know how to restore the Registry in case of a problem. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 (http://support.microsoft.com/kb/256986/) Microsoft Windows registry description

This page

• Summary
  • Steps for configuring the connection server to Oracle
  • Common error messages and how to troubleshoot these errors
• Reference

Show all | close all

Summary

This step-by-step section describes how to set up a link server from a computer running Microsoft SQL server to an Oracle database...

This section describes how to set up a connection server from a computer running Microsoft SQL server to an Oracle database step by step, basic troubleshooting steps are also provided for common errors you may encounter when setting up a linked server to Oracle.

Back to the top to set the connection server to Oracle

Back to Top

1. You must install the Oracle client software on the SQL server computer on which you want to set the link server.
2. Install the required driver on the computer running SQL Server. Microsoft only supports Microsoft ole db providers and Microsoft ODBC drivers for Oracle. If you use a third-party provider or third-party driver to connect to Oracle, You must contact the corresponding supplier when you encounter any problems using these providers or drivers.
3. If you use the Microsoft ole db provider and Microsoft ODBC driver for Oracle, consider the following:
   • The ole db provider and ODBC driver provided by the Microsoft Data Access Component (MDAC) both require SQL * Net 2.3.XOr later. You must install Oracle 7.3 on the client computer.XClient software or later. The client computer is the computer that runs SQL Server.
   • Make sure that you have installed MDAC 2.5 or later on the computer running SQL Server. If you use MDAC 2.1 or earlier, you cannot connect to Oracle 8.XOr a later version of the database.
   • To enable MDAC 2.5 or later to work with the Oracle client software, you must modify the registry of the client computer running SQL Server as described in the following table.

     Microsoft Windows NT, oraclemicrosoft Windows 95, clientwindows 98, and Windows 98 SE Microsoft Windows 2000 2.167.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" = "external "" 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" = "maid" "oraclesqllib" = "orasql8.dll" "oraclesqllib" = "orasql8.dll" "oracleocilib" = "OCI. DLL "" oracleocilib "=" OCI. DLL"

4. After installing the Oracle client software, restart the computer that runs SQL Server.
5. On the computer running SQL Server, use the following script to set the link to the server.

   -- 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_linkedserversEXEC sp_helpserverselect * from sysservers

Note:: If you use the Microsoft ODBC driver for Oracle, you can use@ DatasrcThe parameter specifies the DSN name. For a connection without a DSN@ ProvstrParameters provide the provider string. If you use the Microsoft ole db provider for OracleTnsnames. oraThe Oracle Server alias configured in the file is used@ DatasrcParameters. For more information, see the "sp_addmediaserver" topic in SQL Server books online.

Return to the top of the page and troubleshoot common errors.

Warning: If the registry is incorrectly modified using the Registry Editor or other methods, it may cause serious problems. These problems may need to be solved by re-installing the operating system. Microsoft cannot guarantee that you can resolve these problems. You are at your own risk to modify the registry.

You can use either of the following methods to retrieve extension information about errors encountered during distributed queries.

• Method 1
  In the query analyzer, run the following code to open the trace mark 7300.

  DBCC Traceon(7300)

• Method 2
  Captures the "oledb error" event found in the "error and warning" event category of the SQL event probe. The error message format is as follows: interface: method failed with hex-error code.

  You can find the hexadecimal error code in the oledberr. h file attached to the MDAC software development kit (SDK.

The following lists the ten common error messages that may occur and how to troubleshoot these errors.

Note:: For performance-related issues, search for the "optimizing distributed queries" topic in SQL Server books online to optimize distributed queries.

• Message 1
  Error 7399: ole db Provider '% ls' reported an error. % ls

  Enable tracking flag 7300, or use the SQL event probe to capture the "oledb error" event to retrieve extended oledb error messages.

• Message 2a"ORA-12154: TNS: cocould 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 solve the Oracle connection problem, click the following article number to view the article in the Microsoft Knowledge Base:

  259959 (http://support.microsoft.com/kb/259959/) info: Technology for debugging connection issues with Oracle servers using ODBC drivers and ole db providers
• Message 3Error 7302: cocould not create an instance of ole db Provider 'msdaora'

  Make sure that the msdaora. dll file is correctly registered. (Msdaora. dll is a Microsoft ole db provider for Oracle files .) Use regsvr32.exe to register the Microsoft ole db provider for Oracle. If the registration fails, reinstall the Microsoft Data Access Component (MDAC ). For more information about MDAC, visit the following Microsoft Developer Network (msdn) Website:

  Http://msdn.microsoft.com/data/Default.aspx (http://msdn.microsoft.com/data/Default.aspx)

  Note:: If you are using a third-party program and the Oracle provider cannot run outside the SQL server process, change the Provider Option to run it within the process. To change the provider options, use either of the following methods.

  • Method 1
    Find the following registry key. ThenAllowinprocessThe value of (DWORD) is changed to 1. This registry entry is located under the corresponding provider name:
    HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ providers \ providername
  • Method 2
    When adding a new linked server, you can directly set it through the SQL Server Enterprise Manager.Allow inprocess. ClickProgram OptionsAnd then click to selectAllow inprocessCheck box.
• Message 4Error 7303: cocould 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].

  The error message indicates that the linked server does not have a correct logon ing. You can executeSp_helplinkedsrvloginStored Procedures to correctly set logon information. In addition, verify that you have specified the correct parameters for the linked server configuration.

• Message 5Error 7306: cocould 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 the preceding error message is received, it indicates that a table may be missing in Oracle architecture or you may not have the permission on the table. Verify that the schema name is in uppercase. The case sensitivity of tables and columns should be the same as that specified in Oracle system tables.

  On the Oracle side, tables or columns that are not created using double quotation marks are stored in the form of an uppercase/lowercase letter. If a table or column is enclosed in double quotation marks, the table or column is stored as is.

  The following call shows whether a table exists in the Oracle architecture. This call also displays the exact table name.

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

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

  240340 (http://support.microsoft.com/kb/240340/) PRB: SQL distributed queries on Oracle result in a "cocould not Open Table" error
• Message 6Error 7413: cocould 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 taken from SQL Server books online:

  This error message indicates that, without explicit login ing, you attempt to perform a Distributed Query on Microsoft Windows Authentication logon. In operating system environments that do not support security delegation, Windows NT authentication login needs to be explicitly mapped to useSp_add1_srvloginThe created remote Logon account and password.
• Message 7Error 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 encounter the problem described in the following Microsoft Knowledge Base Article:

  243027 (http://support.microsoft.com/kb/243027/) fix: Number columns in Oracle cause error 7354
• Message 8Error 7356: ole db Provider 'msdaora 'supplied inconsistent metadata for a column. metadata information was changed at execution time.

  If your linked server query uses the Oracle view, you may encounter the following problems described in the Microsoft Knowledge Base Article:

  251238 (http://support.microsoft.com/kb/251238/) PRB: Distributed Query Returns Error 7356 and msdaora
• Message 9Error 7391: The operation cocould not be performed med 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 version is correctly registered as described above.

  Note:: If all the registry items are correct, the mtxoci. dll file will be loaded. If mtxoci. dll is not loaded, you cannot use the Microsoft ole db provider for Oracle or the Microsoft ODBC driver to execute distributed transactions on Oracle. If you are using a third-party provider and receive error 7391, verify that the ole db Provider you are using supports distributed transactions. If the ole db provider does support distributed transactions, verify that Microsoft Distributed Transaction Coordinator (MSDTC) is running.

• Message 10Error 7392: cocould 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 taken from SQL Server books online:

  The ole db Provider Returns Error 7392 because only one transaction is active for this session. This error indicates that when the connection is in an explicit or implicit transaction and the ole db provider does not support nested transactions, you attempt to execute a data modification Statement on the ole db Provider. SQL Server requires such support to terminate the impact of data modification statements and continue transaction processing in case of certain errors.

  If set xact_abort is on, SQL server does not need support for nested transactions in the OLE DB Provider. Therefore, set xact_abort to on before you execute a data modification statement for a remote table in an explicit or implicit transaction. This aims to prevent the ole db Provider you are using from supporting nested transactions.

Back to Top

Reference

For more information, click the following article number to view the article in the Microsoft Knowledge Base: 244661 (http://support.microsoft.com...

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

244661 (http://support.microsoft.com/kb/244661/) info: Limitations of Microsoft Oracle ODBC drivers and ole db providers 259959 (http://support.microsoft.com/kb/259959/) info: technology for debugging connection problems with Oracle servers using ODBC drivers and ole db providers 239719 (http://support.microsoft.com/kb/239719/) info: for Oracle W. r. t Oracle 8.x support for Microsoft ODBC drivers/ole db providers 193893 (http://support.microsoft.com/kb/193893/) Information about using Oracle with Microsoft Transaction Server and COM + components 191168 (http://support.microsoft.com/kb/191168/) info: error "-2147168246 (8004d00a)": unable to register the transaction of the calling object

For more information about how to use the linked server with DB2, click the following article number to view the article in the Microsoft Knowledge Base:

218590 (http://support.microsoft.com/kb/218590/) INF: configure the data source 216428 (http://support.microsoft.com/kb/216428/) configuration for the Microsoft ole db provider for DB2 back to the top