How-to-set-up-and-troubleshoot-a

Document Sample
How-to-set-up-and-troubleshoot-a Powered By Docstoc
					How to set up and troubleshoot a linked
server to an Oracle database in SQL
Server
View products that this article applies to.
Article ID : 280106
Last Review : December 23, 2005
Revision      : 7.2
This article was previously published under Q280106
Important This article contains information about how to modify the registry. Make
sure to back up the registry before you modify it. Make sure that you know how to
restore the registry if a problem occurs. 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/) Description of the Microsoft
Windows registry

On This Page

 SUMMARY
 Steps to set up a linked server to Oracle
 Common error messages and how to troubleshoot them
 REFERENCES

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.

 Back to the top

Steps to set up a linked server to Oracle

 Back to the top

1 You must install the Oracle client software on the computer that is running SQL
. Server where the linked server is set up.
  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
2
  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.
  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'
5
. -- 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.

 Back to the top

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 (http://support.microsoft.com/kb/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
  (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\P
     roviderName
     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 (http://support.microsoft.com/kb/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 (http://support.microsoft.com/kb/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 (http://support.microsoft.com/kb/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.

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:77
posted:2/16/2011
language:English
pages:6