SQL SERVER 2012/2014 連結到 SQL SERVER 2000的各種坑

原文: SQL SERVER 2012/2014 連結到 SQL SERVER 2000的各種坑

       本文總結一下SQL SERVER 2012/2014連結到SQL SERVER 2000的各種坑，都是在實際應用中遇到的疑難雜症。可能會有人說怎麼還在用SQL SERVER 2000，為什麼不更新呢？ 每個公司都會有一兩個幾乎快被人遺忘的系統，接手維護這些系統的人可能都不知換了多少批了。它們的命運注定慢慢消亡。然而偏偏卻又生命力頑強，總還有一些人在使用着這些系統。是以就處在一種尴尬的境地： 更新吧，價值不大，可能再過一兩年，這系統就要被其它系統替代了。而且項目經理也沒有精力、人手耗費在這上面。最重要的是擔心風險問題。因為接手維護的人對這些系統都不甚了解。如果貿然更新，可能風險很大。

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

    自SQL Server 2012開始，已經不支援通過連結伺服器連結到SQL Server 2000。主要是SQL SERVER 2012/2014安裝的是SQL Server Native Client 11.0。而SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions。關于這個

官方文檔

已有說明，具體如下所示：

This topic discusses how various data-access components can be used with SQL Server Native Client.

Server Support SQL Server Native Client 11.0 supports connections to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and Windows Azure SQL Database. Supported Operating System Versions The following table lists which operating systems support SQL Server Native Client.

SQL Server Native Client version	Supported operating systems
SQL Server Native Client (SQL Server 2005)	Microsoft Windows 2000 Service Pack 4 or later Microsoft Windows Server 2003 or later Microsoft Windows XP Service Pack 1 or later Microsoft Windows Vista (requires SQL Server Service Pack 2, or later) Microsoft Windows Server 2008 (requires SQL Server Service Pack 2, or later)
SQL Server Native Client 10.0 (SQL Server 2008)	Microsoft Windows Server 2003 Service Pack 2, or later Microsoft Windows XP Service Pack 2, or later Microsoft Windows Vista Microsoft Windows Server 2008
SQL Server Native Client 10.5 (SQL Server 2008 R2)	Microsoft Windows XP Service Pack 2 or later Microsoft Windows 7
SQL Server Native Client 11.0 (SQL Server 2012)	Microsoft Windows 8 Microsoft Windows Server 2012

此時需要安裝SQL Server Native Client 10. 我在這篇文章 SQL SERVER 2012連結到SQL SERVER 2000的問題解決案例 裡面介紹了如何安裝SQL Server Native Client 10

2： 即使安裝了SQL Server Native Client 10，依然不能使用下面正常的建立連結伺服器的方法建立

EXEC master.dbo.sp_addlinkedserver @server = N'server_name', @srvproduct=N'SQL Server'      

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server_name',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'      

GO      

3：以下面方式建立連結伺服器，能夠成功建立連結伺服器，測試連結也OK，似乎一切OK

EXEC master.dbo.sp_addlinkedserver @server = N'server_name', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI10', @provstr=N'DRIVER={SQL Server Native Client 10.0};SERVER=192.168.xxx.xxx;'      

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server_name',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'      

GO      

但是在調用連結伺服器時，就會出現下面錯誤。

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI10" for linked server "xxxxx" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "SQLNCLI10" for linked server "xxxxx".

配置

4: OpenDataSource  SQL Server Native Client 11.0 does not support connections to SQL Server 2000

如果代碼裡面有使用OpenDataSource，那麼就會報上面的錯誤。此時必須修改為連結伺服器方式通路。

SQL SERVER 2012/2014正确連結到SQL SERVER 2000的方法，

EXEC master.dbo.sp_addlinkedserver @server = N'Server_Name', @srvproduct=N'sqlserver', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server Native Client 10.0};SERVER=192.168.xxx.xxx;'      

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server_Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'UserName',@rmtpassword='########'      

GO      

參考資料：

https://social.msdn.microsoft.com/Forums/en-US/7352802d-5294-45e1-999e-8749a38952eb/linked-server-sql-2012-to-2000-error-microsoft-distributed-transaction-coordinator-ms-dtc-has?forum=sqldatabaseengine https://connect.microsoft.com/SQLServer/feedback/details/731869/using-sqlncli10-to-create-a-linked-server-to-sql-server-2000-causes-a-fault https://msdn.microsoft.com/en-us/library/cc280356(v=SQL.110).aspx