Linked Server to SQL Server 2000

“SQL Server 2012″ has stopped connecting to “SQL Server 2000″ via linked Servers. As this new version uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only.

So for creating linked server we have to work around.

First create a System DSN for connecting SQL Server 2000 Database in the server where SQL Server 2012 is installed. Now create Linked Server like

EXEC master.dbo.sp_addlinkedserver 

@server = N'NorthWind2000', 

@srvproduct=N'MSSQL', 

@provider=N'SQLNCLI', 

@provstr=N'PROVIDER=SQLOLEDB;

SERVER=NorthWind'

 

EXEC master.dbo.sp_addlinkedsrvlogin 

@rmtsrvname=N'NorthWind2000',

@useself=N'True',

@locallogin=NULL,

@rmtuser=NULL,

@rmtpassword=NULL