Creating Linked Server from SQL 2012 to SQL 2000

We all know that creating linked server from SQL 2012 to SQL 2000 does not work using the new native client SQLNCLI11. This client only connects to the following: 2005, 2008, 2008R2. Currently for one of my clients, we are virtualizing the environment to OS & SQL 2012, thus the need for this blog to show the workaround. I will highlight the steps needed to be performed on Win 2012.

Test Scenario

Old Source – SQL 2012(Sp2 with hotfix) on Win 08R2 || Destination – SQL 2000(Sp4) on Win 03

New Source – SQL 2012(Sp2 with hotfix) on Win 2012R2|| Destination – SQL 2000(Sp4) on Win 03

  1. Tried to create a net new linked server using the new client and the following error was generated.

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions. OLE DB provide “SQLNCI11” for linked server “xxxxx” returned message “Client unable to establish connection”. (Microsoft SQL Server, Error:22)

  1. Scripted out the linker server (from old source) and tried to execute it on the new source and got the following error. This error indicates that ODBC connection cannot be located.

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “xxxxx“. OLE DB provider “MSDASQL” for linked server “xxxxx” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server , Error:7303)

Here is part of the scripted linked server:

/****** Object:  LinkedServer [xxxxx]    Script Date: 4/7/2015 9:57:51 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'xxxxx', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc=N'xxxxx', @location=N'System'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xxxxx',@useself=N'False',@locallogin=NULL,@rmtuser=N'testmeuser',@rmtpassword='testmepass'

 Workaround

  • RDP to the box
  • Goto Control Panel–> System and Security–> Administrative Tools
  • In Windows 2102 you will see 2 ODBC Data Source
    • ODBC Data Sources (64-bit)
    • ODBC Data Sources (32-bit)
  • Select ODBC Data Sources (64-bit)–> System DSN(tab)
    • Add
    • Select SQL Server
    • Enter
      • Name of DataSource
      • Description (for documentation purposes)
      • Server (the Destination Server)
    • Select your mode of login (in my case we used SQL login for Linked Servers) & provide username and password
    • Leave all options as is on the remaining pages and click on Finish
    • Make sure to Test Data Source

Go back to your linked server script and plug in the values:

  • @provider=N’MSDASQL’
  • @datasrc=N’datasourcename’ (Name of DataSource provided)
  • @rmtsrvname=N’servername’ (Name of Server provided)
  • @rmtuser=N’testmeuser’ (User provided)
  • @rmtpassword=’testmepass’ (Password provided)

This will allow you to create your linked server to SQL 2k using ODBC DSN. Surprisingly there are lot of organizations still using SQL 2k and if you are one of them drop a comment and share with the community of how many instances /dbs.

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

4 Comments on “Creating Linked Server from SQL 2012 to SQL 2000”

  1. Just wanted to say a big thank you for the code. I spent hours on the Internet trying to link an old SQL 2000 and new SQL 2014 and nothing worked.

    3 hours later I found your code and it worked!

    Now I just need to figure out the easiest way of migrating from a SQL 2000 database to SQL 2014

Leave a Reply

Your email address will not be published. Required fields are marked *