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
2  
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
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
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
    
Actually, you can use the SQLNCL10 driver, just make sure to use the correct bit-ness and add it as an "in process" driver. Not supported and you may have problems. – Jonathan Fite Apr 21 at 17:54

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'
share|improve this answer
1  
Typically you'd want to add something more than just a wall o' code. Also, when adding code to an answer, to format it you can select all the code, then press CTRL - K. Check out the help center for more details. – Max Vernon Apr 21 at 17:47

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.