Tell me more ×
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.
up vote 0 down vote favorite

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

2 Answers

up vote 2 down vote

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 at 15:24
1  
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 at 15:32
up vote 0 down vote

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

 
discard

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.