Linked Server to SQL Server 2000 server from SQL Server 2012

Be careful when using SQLNCLI10 as described in connect item

A workaround can be found here

You have to first test the application that is using such SP's from 2012 to 2000. It also depends on where both server's are located and how much data you are pulling out using Linked Servers.

Is there a reason that the other referenced databases cannot be moved to 2012 ?

Here is sample code I used to get this working, hope this helps somebody! Linked Server set from 2000 to 2012.

USE master
GO

-- Drop Existing LinkedServer [CARME]:
EXEC sp_dropserver @server=N'CARME', @droplogins='droplogins'
GO

-- Re-create LinkedServer [CARME] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'CARME', 
                        @srvproduct=N'', 
                        @provider=N'MSDASQL', 
                        @provstr=N'DRIVER={SQL Server};SERVER=CARME;UID=ROSALINDxCARMExREADER;PWD=XXXXXX'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'CARME', 
                          @useself=N'False', 
                          @locallogin=NULL, 
                          @rmtuser='ROSALINDxCARMExREADER', 
                          @rmtpassword='XXXXXX'
GO

Linked Server from SQL Server 2012 to SQL 2000 is not supported natively or directly. SQL Server 2012 comes with new version on native client i.e. SQLNCLI11, which only connect back to SQL 2008R2/2008/2005 versions.

Also even if you install previous native client i.e. SQLNCLI10, it won't work.

So, if you still wan to connect use MSDASQL provider instead of SQLNCLI11, check this blog post to get the solution and more details