Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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 ?

share|improve this answer
We have a lot of databases (30?) across many servers, and a lot of interaction between them, including stored procs, and DTS Packages. I am hoping to be able to slice the upgrade path into smaller pieces to make it more manageable. – PatFromCanada Apr 9 '13 at 15:24
As I mentioned, TEST IT before hand to avoid any surprises. Also, the performance will depend on how much data is being pulled from cross servers/databases. – Kin Apr 9 '13 at 15:32
The ODBC links work fine but are very slow for some queries – PatFromCanada May 12 '14 at 13:43

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

USE master

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

-- Re-create LinkedServer [CARME] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'CARME', 

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'CARME', 
share|improve this answer
Are you saying this allows SQL 2000 to interact with SQL 2012? – PatFromCanada Apr 20 '15 at 16:14

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.