《轉》Sql Server 2005(64位) 連線Sql Server 2000(32位) .

taogchan發表於2012-12-10

這個月總公司收購了一家小公司，這家小公司的資料庫用的是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 打勾。

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