June 18, 2012 at 2:29 pm
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
------------------------------
June 18, 2012 at 2:33 pm
this is the error I get when I try to refresh the catalog:
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
June 20, 2012 at 8:58 am
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.
June 21, 2012 at 9:59 am
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2012 at 10:43 am
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.
July 3, 2012 at 12:26 pm
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
April 19, 2013 at 1:46 am
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.
January 29, 2014 at 4:50 am
July 1, 2014 at 6:57 am
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