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

本文說明如何設定從執行 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 Server2005 Workgroup Edition
原始 KB 編號： 280106

摘要

本文說明如何設定從執行 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 伺服器別名。 For more information, see the "sp_addlinkedserver" topic in SQL Server Books Online.

常見的錯誤訊息及其疑難解答方式

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

• 方法 1

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

  DBCC Traceon(7300)
  

• 方法 2

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

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

  您可以在 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 請遵循下列步驟，為使用 SSMS) 的提供者設定 [允許內 SQL Server Management Studio ( 處理] 選項。

  1. 開啟 SSMS 並連線到您的 SQL Server 實例。
  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 端，不含雙引號建立的數據表或數據行會以大寫方式儲存。 如果數據表或數據行以雙引弧括住，數據表或數據行會依原樣儲存。

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

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

• 訊息 6

  錯誤 7413：無法連線到連結的伺服器 '%ls' (OLE DB 提供者 '%ls') 。 啟用委派或使用目前用戶的遠端 SQL Server 登入。 使用者 '' 的 Msg 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 提供者支援分散式交易，請確認 MSDTC (Microsoft 分散式交易協調器) 正在執行，且已 啟用網路存取。

• 訊息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 伺服器的連線問題進行疑難解答的技術

若要偵錯 Microsoft ODBC driver for Oracle 或 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 版本) 。 適用於 Oracle 的 Microsoft ODBC 驅動程式和 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 (連結的伺服器)