Linked Server to SQL Server 2000 server from SQL Server 2012

Question

I have a new SQL Server 2012 instance, and am migrating a database from SQL Server 2000. Some of the stored procedures in the DB I am moving reference another database back on that 2000 server. What is the best way to handle this?

Would a linked server help? I am having trouble creating a link for testing. Some people recommend using ODBC, others seem to use the SQL Server 10 client driver (SQLNCLI10). Using the SQL Server 10 client driver seems better to me, are there issues installing it on a SQL Server 2012 instance?

Answer 1

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 ?

Answer 2

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

Answer 3

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

Answer 4

This worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N'TestConnect'
    , @srvproduct=N''
    , @provider=N'MSDASQL'
    , @provstr=N'DRIVER={SQL Server};SERVER=TestConnect;Trusted_Connection=no;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TestConnect'
    , @useself=N'False'
    , @locallogin=NULL,@rmtuser=N'ReportReadOnly'
    , @rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'collation compatible'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'data access'
    , @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'dist'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'pub'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'rpc'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'rpc out'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'sub'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'connect timeout'
    , @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'collation name'
    , @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'lazy schema validation'
    , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'query timeout'
    , @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'use remote collation'
    , @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'TestConnect'
    , @optname=N'remote proc transaction promotion'
    , @optvalue=N'true'