在 SQL Server 中設定和疑難解答連結的伺服器至 Oracle 資料庫

本文說明如何從執行 SQL Server Microsoft SQL Server 的電腦將連結的伺服器設定為 Oracle 資料庫，並提供您在設定連結伺服器至 Oracle 資料庫時可能會遇到之常見錯誤的基本疑難解答步驟。

原始產品版本： Microsoft SQL Server 2005 Standard Edition、Microsoft SQL Server 2005 Developer Edition、Microsoft SQL Server 2005 Enterprise Edition、Microsoft SQL Server 2005 Express Edition、Microsoft SQL Server 2005 Workgroup Edition
原始 KB 編號： 280106

摘要

本文說明如何從執行 SQL Server Microsoft SQL Server 的電腦設定連結的伺服器到 Oracle 資料庫，並提供您在設定連結伺服器至 Oracle 時可能會遇到之常見錯誤的基本疑難解答步驟。 本文中大部分的資訊都適用於設定為使用 Microsoft OLEDB Provider for Oracle （MSDAORA） 的環境。 請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 請改用 Oracle 的 OLE DB 提供者。

如需使用 Oracle OLEDB 提供者設定連結伺服器的詳細資訊，請檢閱 如何使用 Oracle 和連結的伺服器啟動並執行。

從 Microsoft 數據存取元件 （MDAC） 2.5 版和更新版本，Microsoft ODBC 驅動程式和 OLE DB 提供者都僅支援 Oracle 7 和 Oracle 8i，但有下列限制：

• 不支援 Oracle 8.x 特定資料類型，例如 CLOB、BLOB、BFILE、NCHAR、NCLOB 和 NVARCHAR2。

• 不支援 Oracle 7.x 和 8.x 伺服器的 Unicode 功能。

• 不支援多個 Oracle 用戶端實例或多個 Oracle 家庭，因為它們依賴 SYSTEM PATH 變數中第一個出現的 Oracle 首頁。

• 使用 ADO 或 OLEDB 不支援從預存程式或批次 SQL 語句傳回多個結果集。

• 不支援巢狀外部聯結。

• 不支援 XML 持續性。

• 使用這些驅動程式不支援大於 8i 的版本。

設定連結伺服器至 Oracle 的步驟

1. 您必須在執行已設定連結伺服器之 SQL Server 的電腦上安裝 Oracle 用戶端軟體。

2. 在執行 SQL Server 的電腦上安裝您想要的驅動程式。 Microsoft只支援 Microsoft OLE DB Provider for Oracle 和 Microsoft ODBC Driver for Oracle。 如果您使用第三方提供者或第三方驅動程式來連線到 Oracle，您必須連絡個別廠商，以取得使用其提供者或驅動程式可能會遇到的任何問題。

3. 如果您使用 Microsoft OLE DB Provider for Oracle 和 Microsoft ODBC Driver for Oracle，請考慮下列事項：

   • Microsoft數據存取元件 （MDAC） 隨附的 OLE DB 提供者和 ODBC 驅動程式都需要 SQL*Net 2.3.x 或更新版本。 您必須在用戶端電腦上安裝 Oracle 7.3.x 用戶端軟體或更新版本。 用戶端電腦是執行 SQL Server 的電腦。

   • 請確定您已在執行 SQL Server 的電腦上安裝 MDAC 2.5 或更新版本。 使用 MDAC 2.1 或舊版時，您無法連線到使用 Oracle 8 的資料庫。 x 或更新版本。

   • 若要啟用 MDAC 2.5 或更新版本，若要使用 Oracle 用戶端軟體，必須在執行 SQL Server 的用戶端電腦上修改登錄，如下表所示。

     Oracle
     Client               Microsoft Windows 2000 and later versions
     --------------------------------------------------------------------------
     
     7.x                  [HKEY_LOCAL_MACHINE\SOFTWARE
                          Microsoft\MSDTC\MTxOCI]
                          "OracleXaLib"="xa73.dll"
                          "OracleSqlLib"="SQLLib18.dll"
                          "OracleOciLib"="ociw32.dll"
     
     8.0                  [HKEY_LOCAL_MACHINE\SOFTWARE
                          \Microsoft\MSDTC\MTxOCI]
                          "OracleXaLib"="xa80.dll"
                          "OracleSqlLib"="sqllib80.dll"
                          "OracleOciLib"="oci.dll"
     
     8.1                  [HKEY_LOCAL_MACHINE\SOFTWARE
                          \Microsoft\MSDTC\MTxOCI]
                          "OracleXaLib"="oraclient8.dll"
                          "OracleSqlLib"="orasql8.dll"
                          "OracleOciLib"="oci.dll"
     

4. 安裝 Oracle 用戶端軟體之後，請重新啟動執行 SQL Server 的電腦。

5. 在執行 SQL 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_linkedservers
   EXEC sp_helpserver
   select * from sysservers
   

   注意

   如果您使用 Microsoft ODBC Driver for Oracle，您可以使用 @datasrc 參數來指定 DSN 名稱。 若為無 DSN 連線，提供者字串會透過 @provstr 參數提供。 使用 Microsoft OLE DB Provider for Oracle 時，請使用 TNSNames.Ora 檔案中針對 @datasrc 參數設定的 Oracle 伺服器別名。 如需詳細資訊，請參閱《SQL Server 在線叢書》中的主題。

常見的錯誤訊息以及如何進行疑難解答

您可以使用下列兩種方法之一來擷取您在執行分散式查詢時遇到之任何錯誤的擴充資訊。

• 方法 1

  使用 SQL Server Management Studio 連線到 SQL Server，然後執行下列程式代碼來開啟追蹤旗標 7300。

  DBCC Traceon(7300)
  

• 方法 2

  擷取位於 SQL Profiler 中「錯誤和警告」事件類別目錄的「OLEDB 錯誤」事件。 錯誤訊息格式如下：

  Interface：：方法失敗，並出現十六進位錯誤碼。

  您可以在 MDAC 軟體開發工具套件 （SDK） 隨附的 Oledberr.h 檔案中查閱十六進位錯誤碼。

以下是可能發生的常見錯誤訊息清單，以及如何針對錯誤訊息進行疑難解答的相關信息。

• 訊息 1

  錯誤 7399：鏈接伺服器 %ls 的 OLE DB 提供者%ls回報錯誤。 %ls

  開啟追蹤旗標 7300 或使用 SQL Profiler 擷取 OLEDB Errors 事件，以擷取擴充的 OLEDB 錯誤資訊。

• 訊息 2a

  “ORA-12154：TNS：無法解析服務名稱”

• 訊息 2b

  「找不到 Oracle（tm） 客戶端和網路元件。 這些元件是由 Oracle Corporation 提供，且屬於 Oracle 7.3.3 版（或更新版本）用戶端軟體安裝的一部分」

  當 Oracle 伺服器發生連線問題時，就會發生這些錯誤。 如需其他疑難解答，請參閱 下方針對 Oracle 伺服器的 連線問題進行疑難解答的技術一節。

• 訊息 3

  錯誤 7302：無法為連結伺服器 「%ls」 建立 OLE DB 提供者 『MSDAORA』 的實例。

  請確定MSDAORA.dll檔案已正確註冊。 （MSDAORA.dll檔案是 Oracle 檔案Microsoft OLE DB 提供者。使用RegSvr32.exe註冊 Microsoft OLE DB Provider for Oracle。

  注意

  如果您使用第三方 Oracle 提供者，且 Oracle 提供者無法在 SQL Server 進程外部執行，請變更提供者選項，使其能夠執行進程內。 若要變更提供者選項，請使用下列其中一種方法：

  • 方法 1 找出下列登錄機碼。 然後，將 AllowInProcess （DWORD） 專案的值變更為 1。 此登錄機碼位於對應的提供者名稱下： HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName。

  • 方法 2 遵循下列步驟，為使用 SQL Server Management Studio （SSMS） 的提供者設定 [允許處理 ] 選項。

  1. 開啟 SSMS 並連線到您的 SQL 受控執行個體。
  2. 在 [物件總管] 中，流覽至 [伺服器對象>連結的伺服器提供者>]。
  3. 以滑鼠右鍵按下您要設定的提供者，然後選取 [ 屬性]。
  4. 在 [提供者選項] 視窗中，核取 [允許處理中] 選項的 [啟用] 方塊。

• 訊息 4

  錯誤 7303：無法初始化連結伺服器 「%ls」 之 OLE DB 提供者 『MSDAORA』 的數據源物件。 [OLE/DB 提供者傳回的訊息：ORA-01017：無效的使用者名稱/密碼;拒絕登入]OLE DB 錯誤追蹤 [OLE/DB 提供者 'MSDAORA' IDBInitialize：：Initialize 傳回0x80040e4d]。

  此錯誤訊息表示連結的伺服器沒有正確的登入對應。 您可以執行 sp_helplinkedsrvlogin 預存程式，以正確設定登入資訊。 此外，請確認您已指定連結伺服器組態的正確參數。

• 訊息 5

  錯誤 7306：無法從連結伺服器 「%ls」 的 OLE DB 提供者 'MSDAORA' 開啟資料表 ' %ls'。 指定的數據表不存在。 [OLE/DB 提供者傳回的訊息：數據表不存在。][OLE/DB 提供者傳回的訊息： ORA-00942： 數據表或檢視不存在]OLE DB 錯誤追蹤 [OLE/DB 提供者 'MSDAORA' IOpenRowset：：OpenRowset 傳回0x80040e37： 指定的數據表不存在。

  錯誤 7312：鏈接伺服器 %ls 的 OLE DB 提供者 %ls' 架構和/或目錄無效。 已提供四部分名稱，但提供者不會公開必要的介面來使用目錄和/或架構。

  錯誤 7313：鏈接伺服器%ls 的提供者%ls 指定無效的架構或目錄。

  錯誤 7314：鏈接伺服器 %ls 的 OLE DB 提供者%ls 不包含資料表%ls。 該資料表不存在，或是目前的使用者沒有使用該資料表的權限。

  如果您收到這些錯誤訊息，Oracle 架構中可能會遺漏數據表，或您可能沒有該數據表的許可權。 使用大寫確認架構名稱已輸入。 數據表和數據行的字母大小寫應該如 Oracle 系統數據表中所指定。

  在 Oracle 端，在沒有雙引號的情況下建立的數據表或數據行會儲存在大寫。 如果數據表或數據行以雙引弧括住，數據表或數據行會儲存為 is。

  下列呼叫會顯示數據表是否存在於 Oracle 架構中。 此呼叫也會顯示確切的數據表名稱。

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

• 訊息 6

  錯誤 7413：無法連線到連結的伺服器 '%ls' （OLE DB 提供者 '%ls'）。 請啟用委派，或使用目前使用者的遠端 SQL Server 登入。 使用者 '' 的訊息 18456、層級 14、狀態 1、第 1 行登入失敗。

  這個錯誤訊息表示嘗試使用Microsoft Windows 驗證登入的分散式查詢，而不需要明確的登入對應。 在不支援安全性委派的操作系統環境中，Windows NT 驗證的登入需要明確對應至使用 sp_addlinkedsrvlogin所建立的遠端登入和密碼。

• 訊息 7

  錯誤 7391：無法執行作業，因為鏈接伺服器 「%ls」 的 OLE DB 提供者 『MSDAORA』 無法開始分散式交易。 OLE DB 錯誤追蹤 [OLE/DB 提供者 'MSDAORA' ITransactionJoin：：JoinTransaction 傳回0x8004d01b]

  請確認 OCI 版本已正確註冊，如本文稍早所述。

  注意

  如果登錄專案都正確，則會載入MtxOCI.dll檔案。 如果未載入MtxOCI.dll檔案，您無法使用 Microsoft OLE DB Provider for Oracle 或使用 Microsoft ODBC Driver for Oracle，對 Oracle 執行分散式交易。 如果您使用第三方提供者，而且收到錯誤 7391，請確認您使用的 OLE DB 提供者是否支援分散式交易。 如果 OLE DB 提供者支援分散式交易，請確認Microsoft分散式交易協調器 （MSDTC） 正在執行，並 啟用網路存取。

• 訊息8

  錯誤 7392：無法啟動連結伺服器 「%ls」 之 OLE DB 提供者 『MSDAORA』 的交易。 OLE DB 錯誤追蹤 [OLE/DB 提供者 'MSDAORA' ITransactionLocal：：StartTransaction 傳回0x8004d013： ISOLEVEL=4096]。

  OLE DB 提供者傳回錯誤 7392，因為此會話只能有一個交易作用中。 此錯誤表示當連接處於明確或隱含交易中，且 OLE DB 提供者不支援巢狀交易時，正針對 OLE DB 提供者嘗試數據修改語句。 SQL Server 需要這項支援，以便在某些錯誤狀況下終止數據修改語句的影響，同時繼續進行交易。

  如果 SET XACT_ABORT 為 ON，SQL Server 不需要 OLE DB 提供者的巢狀交易支援。 因此，在您針對隱含或明確交易中的遠端資料表執行資料修改語句之前，請先執行 SET XACT_ABORT ON 。 萬一您使用的 OLE DB 提供者不支援巢狀交易，請執行此動作。

針對 Oracle 伺服器的連線問題進行疑難解答的技術

若要偵錯 Oracle Microsoft ODBC 驅動程式或 Microsoft OLE DB Provider for Oracle 的 Oracle 聯機問題，請遵循下列步驟：

1. 使用 Oracle SQL Plus 公用程式（命令行型查詢公用程式）來確認您可以連線到 Oracle 並擷取數據。

   注意

   如果您無法連線到 Oracle 並擷取數據，則表示您安裝或設定 Oracle 用戶端元件不正確，或者當您使用 SQL*Net Easy Configuration 或 Oracle Net8 Easy Configuration 公用程式時，未正確建立 Oracle 伺服器的透明網路基底 （TNS） 服務別名。 請連絡您的 Oracle 資料庫管理員 （DBA），確認您必須正確安裝並設定的 Oracle 元件。

2. 確認電腦上安裝的 Oracle 用戶端 （SQL*Net 版本） 版本。 Microsoft ODBC Driver for Oracle 和 Microsoft OLE DB Provider for Oracle 都需要在用戶端計算機上安裝 SQL*Net 2.3 版或更新版本。

   SQL Plus 的連線能力（Oracle 用戶端查詢工具）可能會正常運作，但您必須重新啟動計算機，讓 ODBC/OLE DB 連線正常運作。

   注意

   當您使用 Oracle 8i 時，.rgs 檔案是空的。

3. 如果 Oracle 用戶端已安裝，而且您收到錯誤指出 Oracle 用戶端元件 7.3 或更新版本必須安裝在電腦上，請確認用戶端電腦上的環境變數 PATH 包含 Oracle 用戶端安裝所在的資料夾，例如， Oracle_Root\Bin。 如果您找不到此資料夾，請將資料夾新增至PATH變數，以解決錯誤。

4. 確認Ociw32.dll檔案位於 Oracle_Root\bin 資料夾中。 此.dll檔案不能存在於用戶端電腦上的任何其他位置。 請確定 Oracle 用戶端元件 DLL（例如，Core40.dll檔案和 Ora*.dll 檔案）不存在於Oracle_Root資料夾或子資料夾之外。

5. 確認電腦上已安裝單一 Oracle 用戶端版本。 相同客戶端電腦上不能有多個版本的 SQL*Net 干擾和重大作業（例如 TNS 和別名查閱）。

6. Microsoft建議您有 Oracle 用戶端的本機安裝，而不是透過對應電腦上的遠端 Oracle 用戶端，然後將它包含在系統的路徑中，以透過 ODBC/OLE DB 連線到 Oracle。 但是提供者和驅動程式會使用本機安裝的 Oracle 用戶端進行測試，而不是在網路共用上測試。

另請參閱

• Microsoft SQL Server 的驅動程式歷程記錄

• 連結的伺服器 (Database Engine)