Linked server from SQLServer2012 to SQLServer2000

by

Linked server from SQLServer2012 to SQLServer2000.

How to create Linked server from SQLServer2012 to SQLServer2000.
When you create linked server from SQLServer2012 to SQLServer2000 you cannot use SQL Native client 11 because it cannot connect with  SQL Server 2000
Each version of SQL native client will support the version of SQL Server that it ships with, two earlier versions and two later versions
Ref: http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx
When you use SQLOLED it will switch to latest SQLNCLI.
Reference: http://msdn.microsoft.com/en-us/library/ms190479.aspx
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
You create a data source and use MSDASQL (Microsoft OLE DB Provider for ODBC) as long as OS supports it.
Sample
sp_addlinkedserver ‘Server\Shiloh’, @provider = N’MSDASQL’, @datasrc=N’SQL2000datasource’, @location=N’System’,@srvproduct=N’MSDASQL’;
Alternatively you can use SQL Server native client 10.0 but it expects “begin distributed transaction” for all the queries.
Advertisement