SQL Server 2022 與 SQL Server 2000 的連結伺服器設定

作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

最近被學員遇到一個問題，就是他試圖用SQL Server 2022設定連結伺服器 (Linked Server) 到 SQL Server 2000，卻沒有辦法連線成功，會出現錯誤訊息，所以本期就針對這方面的主題進行了解。

連結伺服器

有使用過 SQL Server 的人應該都了解，當你的資料放置在多台 SQL Server 中，需要將這些資料串連起來查詢，就必須設定所謂的連結伺服器。連結伺服器說穿了，只不過是在 SQL Server 上「註冊」一台非本機的 SQL Server 罷了，但要知道，連結伺服器並不是只能連到 SQL Server，它在設定的時候就可以是要連到 SQL Server，或是非 SQL Server，如圖：

如果你不是使用管理工具，而是使用 T-SQL 指令的話，則是使用 sp_addlinkedserver的指令，甚至連 Access 資料庫都可以連結，如下：

EXEC sp_addlinkedserver

     @server = N'自訂名稱',

     @provider = N'Microsoft.Jet.OLEDB.4.0',

     @srvproduct = N'OLE DB Provider for Jet',

     @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';

  GO

不過，如果是比較新版的 Access，則需要使用 Ace.OLEDB 來連線了，如下：

EXEC sp_addlinkedserver

     @server = N'自訂名稱',

     @provider = N'Microsoft.ACE.OLEDB.12.0',

     @srvproduct = N'OLE DB Provider for ACE',

     @datasrc = N'C:\MSOffice\Access\Samples\Northwind.accdb';

  GO

那麼，如果設定完連結伺服器之後，該如何連線呢？我們假設你已經把安全性的設定搞定了（因為如果你連線到 Access，預設是不用設定安全性的，因為 Access沒有安全性的設定），那只需要使用：

select * from 自訂名稱…資料表名稱

即可連接到 Access 資料庫中的資料表。

回歸正題

接下來我們回歸正題，如果是 SQL Server 2022要連到 SQL Server 2000的話，該如何設定連接伺服器呢？如果你就直接設定「伺服器類型」為 SQL Server，那麼當你試圖利用

select * from 2000Servername.dbname.dbo.object去存取 SQL Server 2000資料庫中的資料表時，這時就會很殘忍的出現一個錯誤訊息：

OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Client unable to establish connection”.

這時你會不會感覺自己好像設定錯了？

通常的建議是選伺服器類型為「其他資料來源」，然後利用 OLE DB的方式連接到 SQL Server 2000，不過最後的結果依然是出現相同的錯誤訊息，甚至有時你還會加碼看到下面這個錯誤訊息：

Msg 22, Level 16, State 1, Line 0

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.

也就是說，沒有錯， SQL Server 2022之後，不再支援 SQL Server 2000的直接連結伺服器的連結。

那麼，該怎麼辦呢？

由於 SQL Server 2022只支援伺服器類型為 SQL Server 2008R2、SQL Server 2008以及 SQL Server 2005的向前相容兩個版本的 SQL Server連結，所以要嘛你就必須把 SQL Server 2000升級到 SQ Server 2005之後的版本，要嘛就是使用另外一招了，也就是使用 ODBC的方式來連結，不過如果使用 ODBC連結的話通常需要考慮到效能的問題，以及你要設定 system dsn。

由於要使用 ODBC 的連結來連到 SQL Server，所以建議各位使用 T-SQL指令來建立連結伺服器的設定，也就是我們假設你的 SQL Server 2000的伺服器名稱為 VS100，則需要利用sp_addlinkedserver設定的步驟如下：

A的方式：

1. 開啟控制台，搜尋 ODBC，選擇「設定資料來源」（ODBC）。

2. 選擇「系統資料來源名稱」，建立一個系統的 data source name，假設名稱為 VS100DSN。

3. 請務必設定系統資料來源名稱，因為是認這台電腦的，如果是選擇「使用者資料來源名稱」，則只有該使用者才看的到。

4. 設定 data source name時，選擇 SQL Server，並且連向到 SQL Server 2000的電腦名稱，無論使用 Windows驗證或是 SQL驗證都可以，預設資料庫可以不需要設定。

5. 設定完成後使用下面的指令：

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc = N'VS100DSN', @location=N'System';  

這個指令應該就直接把關鍵字改一改就可以直接用了（笑）

比較重要的是 srvproduct 以及 provider，在這裡都是 MSDASQL，所以並不是

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=VS100' 

這樣的設定，因為這裡的 provider是 SQLMCLI，已經無法連線到 SQL Server 2000的環境了，以圖型的介面設定如下：

接下來，還得設定安全性，這裡你可以直接把管理工具打開來設定，或是使用下面的 T-SQL指令：

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VS100',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO

以圖型的介面設定如下：

主要還是利用本機 SQL Server 2022的帳號，對應到 SQL Server 2000的帳號方式進行連線。

B的方式：
如果你不想設定 system DSN的話，也可以直接把設定 system DSN的語法寫在 T-SQL中，也就是如下的設定：

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=VS100;Trusted_Connection=yes;'  

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VS100',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO

如果就管理畫面的角度，就是長這個樣子：

這樣就可以解決 SQL Server 2022連結到 SQL Server 2000的連結伺服器的問題囉，大家可以試試看，不過還是希望大家能儘早升級到 SQL Server 2022啦（笑）。