Linked Server to SQL 2000 from SQL 2012

  • Hi Guys,

    I am able to create linked server to SQL 2008 from SQL 2012 and it works fine; however, I am not able to do so with SQL 2000.TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    The test connection to the linked server failed.

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot create an instance of OLE DB provider "SQLNCLI10" for linked server "TESTSERVER". (Microsoft SQL Server, Error: 7302)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • this is the error I get when I try to refresh the catalog:

    Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

  • I can see no documentation to prove this, but I suspect you are seeing the results of deprecation of 2000. Most 2000 database server management is not possible in 2012 as it is more than 2 versions back, so you may not be able to link a 2000 to a 2012 in this fashion. But again, that's just a guess and not based upon documentation or an actual test.

  • http://connect.microsoft.com/SQLServer/feedback/details/731869/using-sqlncli10-to-create-a-linked-server-to-sql-server-2000-causes-a-fault#details

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Been a while since I had to deal with distributed transactions over linked servers. I found the problem to be in the configuration of MSDTC. I wish I could provide more information, but all I can suggest is Googling (or Binging) MSDTC configuration and see what you get.

  • The SQL Server Native Client (SQLNCLI11) that ships with SQL Server 2012 does not support connections to SQL Server 2000 an earlier. I found this out during testing of our upgrade to SQL 2012. We upgraded our 2008 R2 servers in-place and it appears to have left the SQLNCLI10 provider in place but if you did a fresh install of SQL Server 2012 on a new server you may not have this provider. Does your 2012 server have the SQLOLEDB provider listed under Linked Server Providers in SSMS? If so, you could try using that.

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • I created a new ODBC data source using SQLNCLI10 then created a linked server to SQL 2000 using this new ODBC data source on a server with SQL 2012 and although it displays the DBs and tables under the Linked Server, when I use it to query in TSQL, I get this error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "MSDASQL" for linked server "MYSERVERNAME" reported an error. The provider did not give any information about the error.

    Msg 7312, Level 16, State 1, Line 1

    Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSERVERNAME". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

    Am I creating the ODBC data source correctly - or is it simply impossible to connect to SQL2000 from SQL2012?

    Thanks.

  • I use appraoch #2 and that working fine for me...

    SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround[/URL]

  • I used both suggested methods on a windows server 2012 with SQL2012 Stadard and get the message that the data source cant be initialised

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply