觸發器 同步連結的伺服器的 另一種解決辦法

 

最近需要將本地sql資料觸發同步到遠程伺服器上，  先想到用連結的伺服器， 調試N久一直報下面這個錯誤

SQLOLEDB不能使用分散式交易

無法執行該操作，因為連結的伺服器 "xxxxx"
的 OLE DB
提供者 "SQLNCLI"
無法啟動分散式交易。

 無奈百度 google了好久也沒用解決

最後想到使用xp_cmdshell 來執行sql代碼，不會涉及分散式交易，測試通過了

當然，啟用xp_cmdshell 具有一定安全風險， 如果是公網伺服器大家一定先做好安全工作

 

代碼如下：

 

Alter trigger tgr_test on test2 for insert,update as begindeclare @cmd varchar(300),@sql varchar(200)set @sql='update test set name =''aabb'' '   set @cmd='osql -S 202.xx.xx.xx -U sa -P 密碼 -d 資料庫名 -q "'+@sql+'"'exec master.dbo.xp_cmdshell @cmdend

 

 

 

 

 

 

另外附上

無法執行該操作，因為連結的伺服器 "xxxxx"
的 OLE DB
提供者 "SQLNCLI"
無法啟動分散式交易。

網上解決辦法

二、 解決方案1.       
雙方啟動MSDTC服務

MSDTC服務提供分散式交易服務，如果要在資料庫中使用分散式交易，必須在參與的雙方伺服器啟動MSDTC（Distributed
Transaction Coordinator）服務。

2.       
開啟雙方135連接埠

MSDTC服務依賴於RPC（Remote Procedure Call (RPC)）服務,RPC使用135連接埠，保證RPC服務啟動，如果伺服器有防火牆，保證135連接埠不被防火牆擋住。  

 使用“telnet IP 135
”命令測試對方連接埠是否對外開放。也可用連接埠掃描軟體（比如Advanced Port Scanner）掃描連接埠以判斷連接埠是否開放。

3.       
保證連結的伺服器中語句沒有訪問發起事務伺服器的操作

在發起事務的伺服器執行連結的伺服器上的查詢、視圖或預存程序中含有訪問發起事務伺服器的操作，這樣的操作叫做環回（loopback），是不被支援的，所以要保證在連結的伺服器中不存在此類操作。

4.       
在事務開始前加入set xact_abort ON語句

對於大多數 OLE DB
提供者（包括 SQL Server），必須將隱式或顯示事務中的資料修改語句中的 XACT_ABORT
設定為 ON。唯一不需要該選項的情況是在提供者支援嵌套事務時。

5.       
MSDTC設定

開啟“管理工具――元件服務”，以此開啟“元件服務――電腦”，在“我的電腦”上點擊右鍵。在MSDTC選項卡中，點擊“安全配置”按鈕。

在安全配置視窗中做如下設定：

l        
選中“網路DTC訪問”

l        
在用戶端管理中選中“允許遠程用戶端”“允許遠端管理”

l        
在交易管理通訊中選“允許入站”“允許出站”“不要求進行驗證”

l        
保證DTC登陸賬戶為：NT   Authority\NetworkService

6.       
連結的伺服器和名稱解析問題

建立連結sql server伺服器，通常有兩種情況:

l        
第一種情況，產品選”sql server”

EXEC sp_addlinkedserver

   @server='linkServerName',

   @srvproduct = N'SQL Server'

這種情況，@server
（linkServerName）就是要連結的sqlserver伺服器名或者ip地址。

l        
第二種情況，提供者選“Microsoft OLE DB Provider Sql Server”或“Sql Native Client”

EXEC sp_addlinkedserver  

   @server=' linkServerName ',

   @srvproduct='',

   @provider='SQLNCLI',

   @datasrc='sqlServerName'

這種情況，@datasrc（sqlServerName）就是要連結的實際sqlserver伺服器名或者ip地址。

 

Sql server資料庫引擎是通過上面設定的伺服器名或者ip地址訪問連結的伺服器，DTC服務只通過伺服器名地址訪問連結的伺服器，所以要保證資料庫引擎和DTC都能通過伺服器名或者ip地址訪問到連結的伺服器。

資料庫引擎和DTC解析伺服器的方式不太一樣，下面分別敘述

6.1      
資料庫引擎

第一種情況的@server或者第二種情況的@datasrc設定為ip地址時，資料庫引擎會根據ip地址訪問連結的伺服器，這時不需要做名稱解析。

第一種情況的@server或者第二種情況的@datasrc設定為sql
server伺服器名時，需要做名稱解析，就是把伺服器名解析為ip地址。

有兩個辦法解析伺服器名：

一是在sql server用戶端配置中設定一個別名，將上面的伺服器名對應到連結的伺服器的ip地址。

二是在“C:\WINDOWS\system32\drivers\etc\hosts”檔案中增加一條記錄：

xxx.xxx.xxx.xxx  
伺服器名

作用同樣是把伺服器名對應到連結的伺服器的ip地址。

6.2      
DTC

不管哪一種情況，只要@server設定的是伺服器名而不是ip地址，就需要進行名稱解析，辦法同上面第二種辦法，在hosts檔案中增加解析記錄，上面的第一種辦法對DTC不起作用。

如果@server設定的是ip地址，同樣不需要做網域名稱解析工作。

 

7.      
遠程伺服器上的名稱解析

分散式交易的參與伺服器是需要相互訪問的，發起查詢的伺服器要根據機器名或ip尋找遠程伺服器的，同樣遠程伺服器也要尋找發起伺服器，遠程伺服器通過發起伺服器的機器名尋找伺服器，所以要保證遠程伺服器能夠通過發起伺服器的機器名訪問到發起伺服器。

一般的，兩個伺服器在同一網段機器名能就行很好的解析，但是也不保證都能很好的解析，所以比較保險的做法是：

在遠程伺服器的在“C:\WINDOWS\system32\drivers\etc\hosts”檔案中增加一條記錄：

xxx.xxx.xxx.xxx  
發起伺服器名

 

 

sp_configure 'show advanced options',1reconfiguregosp_configure 'xp_cmdshell',1reconfigurego