連結的伺服器(Linked Server)筆記

之前佈署測試環境，發生了一連串關於"連結的伺服器(Linked Server)"的問題，
第一個問題：
訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。
訊息 7312，層級 16，狀態 1，行 1
不能使用連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 的結構描述或目錄。提供了四部分的名稱，但提供者並未公開必要的介面，以使用目錄或結構描述。

這錯誤訊息的關鍵字不難看出是"提供者"。
點開"提供者"後，滑鼠右鍵點"SQLNCLI10"選屬性即可開啟"提供者選項"


因此，我們看了一下"提供者選項"，發現裡面所有選項都被選取了...
這時候我們拜一下 Google 大神，找到取消"限層級零"與"不允許特定存取"可以解決我們的問題。


對於這些選項說明可以看一下：連結的伺服器屬性(提供者選項頁面)

感覺還會有問題...
果然，隔天又發現有 error，經過檢查發現，又是 Linked Server 的問題。
這次出現什麼碗糕哩！？
連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 傳回訊息 "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."。
訊息 7306，層級 16，狀態 2，行 1
無法從連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 開啟資料表 ""db_name"."dbo"."table_name""。提供者不支援在這個資料來源上的索引掃描。

這訊息的關鍵字"提供者"、"索引"
看了就覺得這和上面的問題有點點關係，因為"索引為存取路徑"也是被勾選的嘛...
將它取消以後，系統便正常了...

目前尚有一個問題未解決，那就是預存程序更新 Linked Server 裡的 Database，
如果沒有加 Transaction，一切正常，加了以後就出現了：
連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 傳回訊息 "異動已經以暗示或者明確的方式認可或中止了"。
訊息 7391，層級 16，狀態 2，行 5
無法執行作業，因為連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 無法開始分散式交易。
這問題比較頭痛一點，因為照著 Google 找到的一些方法，仍然是沒能解決...
解決以後再來更新這篇文章吧...

最後一個問題終於解決，
基本上就是將兩台機器的 MSDTC 開啟，並且做一些設定，
首先進入"元件服務"


接著找到"我的電腦"，然後右鍵點擊選取"內容"


選取 MSDTC 頁籤，並且點選"安全性設定"


將裡面的設定設定成下面畫面


在 Windows Server 2003 SP2 不需要特地去改機碼，
如果你是 SP1 的版本，或是用 Windows XP 測試的話，
請檢查下面這機碼是否存在，其值是否為1

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]
"TurnOffRpcSecurity"=dword:00000001

點此下載檔案

然後，最重要的一個步驟就是，將這兩台機器都重新開機

事實上，在 Microsoft 技術支援 裡面就有說明解法，
只是那時候心急，忽略了最後一個步驟：重新開機...
導致我的問題拖好久...

最後，如果只是加上 Transaction 的話，他應該還是會丟個錯誤出來 XD
連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 傳回訊息 "異動中已經存在有徵募"。
訊息 7395，層級 16，狀態 2，行 2
無法為連結伺服器 "db_host" 的 OLE DB 提供者 "SQLNCLI10" 啟動巢狀交易。因為 XACT_ABORT 選項已設定為 OFF，所以必須要有巢狀交易。

所以我們必須要在 Transaction 之前，將 XACT_ABORT 設定為 ON，就像下面這樣

SET XACT_ABORT ON;
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO [db_host].[db_name].[dbo].[table_name]
           ([id]
           ,[stringChar])
     VALUES
           (123
           ,'123456789abcdef')
COMMIT TRANSACTION

大功告成。