SQL Server 2012 不支持创建连接服务器到 2000 版本，及其替代方案

翻译自“sqlwithmanoj”的博客

http://sqlwithmanoj.wordpress.com/tag/sqlncli10/

小伙伴们震惊了吧，“SQL Server 2012” 不支持通过连接服务器的方式连接到“SQL Server 2000”了。SQL Server 2012 采用了新版本的SQLNCL11（SQL Server Native Client 11）替代了以前的SQLNCL10，这个客户端只支持连接到2008R2，2008，2005版本的数据库。

笔者加一句：这个SQLNCL11不仅不能通过分布查询连接2000版本数据库，还原操作也不能进行，简而言之，不能读取2000版本的数据了。

问题是这样复现的：

我将我的数据库升级从 2008R2 升级到了2012
将从2008R2 服务器备份的数据库文件还原到了 2012服务器上
在新服务器上执行任务时候，出现了这样的错误

错误信息：

OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Client unable to establish connection”.
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.
OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Invalid connection string attribute”.

由于我是升级了数据库，所以我把问题定位到了连接服务器的OLE DB（对象链接嵌入数据库）应用程序接口上，发现了这个SQLNCL11的版本，同时我的服务器上还有SQLNCL10版本。

所以我试着显示使用SQLNCL10去创建连接服务器，但还是出现了如下异常：

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

我贴出我创建连接服务器的脚本，如下：

USE [master]
GO</p>

<p>-- Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

—>以下是替代的解决方案：

使用ODBC是一个比较好的方案，有两种实现方式

在操作系统中穿件一个ODBC Data Source (DSN)，在SQLSERVER中只用这个DSN创建连接服务器。
创建连接服务器时候，直接使用DSN的连接字符串调用ODBC接口。

第一种方式：

首先创建ODBC数据源

打开控制面板，进入管理工具，选择”数据源（ODBC）“
在”ODBC数据源管理器“页签，选取”系统DSN“面板
单击添加创建一个新的数据源
选择”SQL Server“然后点击完成

笔者多嘴2:基本上各大数据库甚至excel都提供了相关接口，都可以连接

在新建数据源页面，建议给数据源适当的命名（比如: NorthWind2000DSN），这个命名会在以后创建练级服务器时候使用到，这里就叫NorthWind，点击下一步
选择验证方式，Windows验证或者Sqlserver都无所谓，进入下一步
选择默认数据库，不选也无所谓，下一步
点击完成，新的DSN就创建了

现在可以使用DSN创建连接服务器了，在@dadasrc参数中使用DSN名称，同时@provider参数中要填写”MSDASQL“.

以下是示例SQL

-- Drop Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO</p>

<p>-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc = N'NorthWind2000DSN', @location=N'System';</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

第二种方式：

直接使用DSN字符串也挺好，在@provstr参数中直接使用DSN连接字符串，请看：

-- Drop Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

使用这种方式可以实现在2012服务器上分布式查询2000版本数据库的数据。

这种方式看似可以直接替代以前的连接服务器，其实效率降低了，如果想实现更有效率的ETL操作，建议升级源数据库的版本，至少到SQL2005。

笔者多嘴3：据不完全统计，ODBC数据源查询效率约为使用SQLNCL查询的1/4。

发表评论 取消回复

发表评论取消回复