Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server to SQL 2000 from SQL 2012 Expand / Collapse
Author
Message
Posted Monday, June 18, 2012 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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
------------------------------
Post #1317581
Posted Monday, June 18, 2012 2:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
this is the error I get when I try to refresh the catalog:

Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
Post #1317583
Posted Wednesday, June 20, 2012 8:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:20 AM
Points: 985, Visits: 1,842
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.
Post #1318781
Posted Thursday, June 21, 2012 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1319430
Posted Thursday, June 21, 2012 10:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:10 PM
Points: 20,876, Visits: 32,919
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1319462
Posted Tuesday, July 3, 2012 12:26 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:01 PM
Points: 295, Visits: 287
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.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #1324602
Posted Friday, April 19, 2013 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:23 AM
Points: 1, Visits: 170
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.
Post #1444260
Posted Wednesday, January 29, 2014 4:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:44 AM
Points: 364, Visits: 276
I use appraoch #2 and that working fine for me...

SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround
Post #1535834
Posted Tuesday, July 1, 2014 6:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:28 AM
Points: 455, Visits: 600
I used both suggested methods on a windows server 2012 with SQL2012 Stadard and get the message that the data source cant be initialised
Post #1587995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse