sqlserver分布式 用觸發器插入資料

標籤：style   blog   http   io   ar   color   os   使用   sp   

這個月總公司收購了一家小公司，這家小公司的資料庫用的是32位的 Sql2000 ，已經使用很長一段時間了，系統也比較穩定。本著節約成本的原則，總公司保留原公司的一套管理系統，但要求重要資料每天上傳到總公司的64位 Sql2005 資料庫中去，這個簡單的工作自然是交給我高升這樣的小人物去完成了。

原想這是個很簡單的差事，可是當我建好 LinkServer 以後卻報出了如下錯誤，從此我的噩夢開始了：

OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
訊息 7311，層級 16，狀態 2，第 1 行
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "TEST_GAO". The provider supports the interface, but returns a failure code when it is used.

error 7311 ： 安裝在伺服器"TEST_GAO" 的版本需要打升級嗎？一查對方的伺服器還是 sp3 的版本，於是下載了一個 sp4 的補丁，可是安裝以後問題依舊。接著我又用自己的電腦上安裝的 Sql2005 和遠端 Sql2000 建立了一個 LinkServer 測試了一下，結果很正常的就連上了，並且可以查詢出資料。這個問題可真是奇怪了，難道發生了什麼靈異事件？我自己的電腦和兩台伺服器都是安裝的 windows server 2003 sp2 的作業系統，為什麼同樣是Sql2005我自己的就能連上，而伺服器上的卻不能連呢？

原來是因為32位的 Sql2000 升級到sp3或者sp4以後還需要手工執行補丁包內的 Instcat.sql 指令碼才能串連上64位的 Sql2005 ，執行 cmd 進入命令列模式，輸入如下語句，完成安裝，這下 LinkServer 能夠正常串連。

輸入 ： osql -U gaosheng -P sheng -S 127.0.0.1 -i C:/sql2ksp4/install/instcat.sql

-U：Sql2000的登陸名，要是具備系統管理權限的，最好是使用sa使用者
-P：密碼
-S：伺服器名或IP地址
-i：指令碼所在的路徑及名稱，注意大小寫。

參考MSDN ： http://support.microsoft.com/kb/906954/zh-tw 

 

LinkServer 建好以後下一步就是查資料寫資料了，Sql語句寫起來是很簡單，可是開啟事務後又出現問題了：

OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
訊息 7391，層級 16，狀態 2，第 1 行
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "TEST_GAO" was unable to begin a distributed transaction.

error 7391 ： 這很顯然由於開啟了事務，需要分散式交易協調器的支援。開啟控制台 - 管理工具 - 服務，將兩台伺服器上的 Distributed Transaction Coordinator (MSDTC) 服務開啟，注意 MSDTC 服務的登陸名必須選擇 NETWORK SERVICE 使用者。然後設定進階選項，運行 dcomcnfg.exe ，進入元件服務，選擇我的電腦右鍵屬性，MSDTC - 安全配置，勾選：網路DTC訪問，允許入站，允許出站，並且不要求進行驗證。其他的可以根據需要選擇，不是必須選項。

OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "No transaction is active.".
訊息 7391，層級 16，狀態 2，第 1 行
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "TEST_GAO" was unable to begin a distributed transaction.

error 7391 ： 怎麼還是這個錯誤，沒有活動事務，無法啟動分散式交易？進階配置也做過了，並且已經不要求進行驗證了，難道這樣還是不行？那就麻煩了，兩台伺服器並不在同一個域中，這樣怎麼讓他相互信任呢，那就需要配置hosts檔案了。

C:/WINDOWS/system32/drivers/etc/hosts　，在系統目錄下找到這個隱藏的hosts系統檔案，在檔案的末尾加上對方的IP地址和主機名稱，如果伺服器是叢集的還要加上cluster的地址和虛擬IP地址，如：

136.16.25.79    GAOSHENG_Cluster
136.16.25.40    GAOSHENG_V1
136.16.25.80    GAOSHENG_DB1

第一個是Cluster主機的IP和主機名稱，第二個是虛擬IP和主機名稱，第三個才是真真實位址，加上前兩個地址是為了保證伺服器宕機以後，Cluster做了容錯移轉LinkServer還能正常使用。如果不是叢集伺服器只需要第三個真真實位址就行了，這樣雙方就能互相信任，正常使用分散式交易了。注意這個hosts檔案兩邊都要配置的，配置的是對方的IP地址和檔案名稱。

OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "Cannot start more transactions on this session.".
訊息 7395，層級 16，狀態 2，第 1 行
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "TEST_GAO". A nested transaction was required because the XACT_ABORT option was set to OFF.

還來！怎麼還是有錯誤啊？
error 7395 ： 還好直接告訴我怎麼做了，在 Begin Transaction 前加上 SET XACT_ABORT ON 即可。

現在總算是不抱錯了，查詢修改也沒問題了，問題終於解決了。建一個簡單的 LinkServer 居然就出現了這麼多的問題，一方面是我學藝不精，另一方面反映出來的是平時都是單伺服器的操作，很多問題根本就是遇不到的，光有理論是遠遠不夠的。不過最後能夠解決問題也反映出我解決問題的能力還是有的，搞不懂就問人，搞得懂就答人，沒有人懂還可以問神。（ MSDN 和 CSDN ）

 

總結：

在實際工作中會出現各種各樣的問題，老是在一台伺服器上測試和學習得到的東西畢竟有限，所以下次遇到問題千萬要自己學著解決，要多鍛煉一下自己解決問題的能力吧。如果沒有多個伺服器的環境學習，我這裡推薦一個個 Microsoft 軟體 Virtual PC ，這個軟體可以在一台電腦上類比很多台電腦出來，有各自的IP，對我們學習多伺服器的操作很有協助。

程式員不光要會學代碼，最重要的還是分析問題解決問題的能力，具備這些能力才能算是一個合格的程式員。當然多多交流也是很有必要的，但前提是有問題自己先動腦子去想，然後才去交流。我高升在這裡也很希望能和叢多的程式員們多交流交流技術，雖然現在水平不高，但一點一點的積累總會有所成就的。

 

寫兩個初次使用 Sql Server 2005 遠端連線時最容易報的錯誤，湊點字數。

OLE DB provider "SQLNCLI" for linked server "TEST" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TEST" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

這是新手在初次使用 Sql Server 2005 最最常見的一個錯誤，原因是預設的配置禁止了遠端連線。很多對資料庫不是很瞭解的人不瞭解如何去配置，所以 Sql Server 2005 提供了這個"介面區配置器"中設定，把遠端連線方式改成本地串連和遠端連線，僅使用 TCP/IP ，注意這個選項改動以後是需要重啟 SqlServer 的服務才會生效的。另外還有一個很重要的服務 SQL Server Browser ，開啟了這個服務用戶端才能正常訪問。

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource‘ of component ‘Ad Hoc Distributed Queries‘ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries‘ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries‘, see "Surface Area Configuration" in SQL Server Books Online.

這個錯誤也是因為 Sql Server 2005 預設的配置的問題，預設關閉了使用 OpenRowset/OpenDatasource 的許可權。解決也是在"介面區配置器"中配置，將即席遠程查詢中的開啟 OpenRowset/OpenDatasource 打勾。

當然這個也可以使用以下代碼，執行如下代碼：

sp_configure ‘show advanced options‘, 1;  --開啟進階配置
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries‘, 1;  --開啟即席查詢
GO
RECONFIGURE;
GO
sp_configure ‘show advanced options‘, 0;  --關閉進階配置
GO
RECONFIGURE;
GO 另外的一些基礎配置:1、關閉雙方防火牆
2、啟動雙方MSDTC服務
3、事務開始前加入set xact_abort ON語句
4、管理工具――元件服務設定允許遠程用戶端、允許遠端管理、允許入站、允許出站、不要求進行驗證
5、DTC登陸賬戶為：NT   Authority\NetworkService
6、伺服器和名稱解析，我這裡直接用的ip地址應該不存在這個問題      下面是自己的一點經驗:一.上面的按照原來的做法 做到 OLE DB provider "SQLNCLI" for linked server "TEST_GAO" returned message "No transaction is active.".這個地方就過不去了,折騰了一下午,最後用Dtcping.exe進行檢測後發現問題,搞定。 具體的問題就是RCP拒絕訪問，解決方案如下:
方法如下：
1．單擊“開始”，單擊“運行”，鍵入“gpedit.msc”，然後單擊“確定”，開啟組策略。
2．開啟組策略後，選擇“電腦配置->系統管理範本->系統->遠程序呼叫->用於未驗證的RPC用戶端的限制”。
3．按右鍵“用於未驗證的RPC用戶端的限制”，在彈出的右鍵菜單裡選擇“屬性”，彈出“屬性”對話方塊。在“設定”屬性頁面裡選擇“已啟用”，“要應用的RPC運行時未驗證的用戶端限制”選擇“無”。單擊“確定”完成設定。(這一步在windows server 2008中是雙擊用於未驗證的RPC用戶端的限制,然後開啟相應配置,其他同此步的配置)
二.在配置host檔案的時候主機名稱用ipconfig -all來確定。  下面是具體的代碼

create trigger oppor_inserton [OpportunityBase]    for insert --插入觸發as    --定義變數    declare @id uniqueidentifier, @name varchar(300);    --在inserted表中查詢已經插入記錄資訊    select @id = [OpportunityId], @name = Name from inserted;    insert into COMMONDB.common_opportunity values(@id, @name);    print ‘添加成功！‘;go

create trigger oppor_inserton [Opportunity]    for insert --插入觸發as    --定義變數    declare @id uniqueidentifier, @name varchar(300);    --在inserted表中查詢已經插入記錄資訊    select @id = [OpportunityId], @name = Name from inserted;    SET XACT_ABORT ON      begin distributed tran    insert into REMOTESERVER.CommonDB.dbo.common_opportunity(id,name) values(@id, @name);    commit tran    print ‘添加成功！‘;go

 

  

sqlserver分布式 用觸發器插入資料