SQL Server 2012/2014 links to various pits in SQL Server 2000

This article summarizes the various pits in SQL Server 2012/2014 that are linked to SQL Server 2000, all of which are problems encountered in practical applications. Someone might say why SQL SERVER 2000 is still in use, why not upgrade it? Each company will have one or two almost forgotten systems, and the people who take over the system may not know how many batches have been changed. Their destiny is doomed to fade away. However, but also the vitality tenacious, there are always some people are using these systems. So it is in an awkward position: upgrade it, the value is not big, perhaps a year or two, the system will be replaced by other systems. And the project manager has no energy or manpower to spend on it. The most important thing is to worry about risk. Because the people who took over the maintenance didn't know much about these systems. If you escalate, you may be at great risk.

1:sql Server Native Client 11.0 does not support connections to SQL Server or earlier versions

Starting with SQL Server 2012, it is not supported to link to SQL Server 2000 through a linked server. The primary SQL Server 2012/2014 installation is SQL Server Native Client 11.0. While the SQL Server Native Client 11.0 does not the connections to SQL Server or earlier versions. About this official document

It has been described as follows:

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

Server Support

SQL Server Native Client 11.0 supports connections to SQL Server 2005, SQL Server, SQL Server R2, SQL Server 201 2, 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 4 Service Pack 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 (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 R2)	Microsoft Windows Server 2003 Service Pack 2, or later Microsoft Windows XP Service Pack 2 or later Microsoft Windows Vista Microsoft Windows Server 2008 Microsoft Windows 7
SQL Server Native Client 11.0 (SQL Server 2012)	Microsoft Windows Vista Microsoft Windows Server 2008 Microsoft Windows 7 Microsoft Windows 8 Microsoft Windows Server 2012

SQL Server Native Client 10 needs to be installed at this time. I am in this article in SQL Server 2012 link to SQL Server 2000 problem resolution case that describes how to install SQL Server Native Client 10

2: Even if SQL Server Native Client 10 is installed, it is still not possible to use the following general method of establishing a linked server

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: The following way to establish a linked server, the ability to successfully create a linked server, testing the link is OK, it seems all OK

EXEC master.dbo.sp_addlinkedserver @server = N' server_name ', @srvproduct =n' SQL Server ', @ 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

However, the following error occurs when you invoke the linked server.

MSG 7399, Level A, State 1, line 1

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

MSG 7301, Level A, state 2, line 1

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

Configuration

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

If you use OpenDataSource in your code, you will be quoted the above error. You must modify the access as a linked server at this time.

SQL Server 2012/2014 correctly links to SQL Server 2000 methods,

EXEC master.dbo.sp_addlinkedserver @server = N' server_name ', @srvproduct =n' SQL Server ', @ 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

Resources:

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

SQL Server 2012/2014 links to various pits in SQL Server 2000