locked
Linked Servers between SQL Server 2012 and SQL Server 2002 RRS feed

  • Question

  • Hi All,

    Good Day!

    We have created a linked server b/w SQL server 2005 & SQL server 2000 and its working fine. Now we are migrating SQL Server 2005 database to SQL Server 2012 SP2 version.

    How do me create same linked server b/w sql server 2012 & SQL Server 2000.

    Thanks

    Raghu

    Monday, August 17, 2015 10:24 PM

Answers

  • You wont be able to create a linked server to SQL 2000 from SQL2012 using SQL Native Client (OLE DB Provider) since SQL2012 does not support SQL2000...

    There are a couple of workarounds for this, one of them is using ODBC connection for your linked server object and another is using MSDASQL provider when creating linked server object.

    Check out the link below for step by step:

    http://sqlwithmanoj.com/2012/12/10/sql-server-2012-does-not-support-linked-server-to-sql-server-2000-workaround/

    • Proposed as answer by Milan Das Tuesday, August 18, 2015 2:12 AM
    • Marked as answer by pituachMVP Tuesday, August 25, 2015 7:23 PM
    Monday, August 17, 2015 10:32 PM
  • Hi Raghu,

    According to your description, you need to create linked server to SQL Server 2000 from SQL Server 2012, right?

    As others said, SQL Server uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only. So first you need to install an older version of the SQL Native Client OleDB provider, SQLNCLI10. 

    Here is a similar thread with yours. Please refer to Alberto Morillo's solution on the link below which was verified by others users.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2e02c603-e28d-49eb-b073-548c59732b5d/linked-server-from-sql2012-to-sql2000

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Tuesday, August 18, 2015 2:48 AM
    • Proposed as answer by Shanky_621MVP Tuesday, August 18, 2015 4:35 AM
    • Marked as answer by pituachMVP Tuesday, August 25, 2015 7:23 PM
    Tuesday, August 18, 2015 2:47 AM

All replies

  • You wont be able to create a linked server to SQL 2000 from SQL2012 using SQL Native Client (OLE DB Provider) since SQL2012 does not support SQL2000...

    There are a couple of workarounds for this, one of them is using ODBC connection for your linked server object and another is using MSDASQL provider when creating linked server object.

    Check out the link below for step by step:

    http://sqlwithmanoj.com/2012/12/10/sql-server-2012-does-not-support-linked-server-to-sql-server-2000-workaround/

    • Proposed as answer by Milan Das Tuesday, August 18, 2015 2:12 AM
    • Marked as answer by pituachMVP Tuesday, August 25, 2015 7:23 PM
    Monday, August 17, 2015 10:32 PM
  • Hi Raghu,

    According to your description, you need to create linked server to SQL Server 2000 from SQL Server 2012, right?

    As others said, SQL Server uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only. So first you need to install an older version of the SQL Native Client OleDB provider, SQLNCLI10. 

    Here is a similar thread with yours. Please refer to Alberto Morillo's solution on the link below which was verified by others users.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2e02c603-e28d-49eb-b073-548c59732b5d/linked-server-from-sql2012-to-sql2000

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Tuesday, August 18, 2015 2:48 AM
    • Proposed as answer by Shanky_621MVP Tuesday, August 18, 2015 4:35 AM
    • Marked as answer by pituachMVP Tuesday, August 25, 2015 7:23 PM
    Tuesday, August 18, 2015 2:47 AM