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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy