July 10, 2012 at 6:44 pm
We installed SQL 2012 recently... linked server between SQL 2012 and SQL 2008 using SQLNCLI11 is working fine; however, we have issues creating linked server between SQL 2000 and SQL 2012.
Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
I am not sure why I am getting this error when the linked server with SQL 2008 is doing fine and there is not MS DTC error also linked server using the same SQL 2000 server with SQL 2008 server is working fine as well so I don't think there is issue with configuring MSDTC.
Thanks a lot for your help.
July 12, 2012 at 12:35 am
You have to make sure SQLNCLI10 is used, as SQLNCLI11 doesn't support connections to SQL Server 2000. Which provider did you choose in the Linked Server menu?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 12, 2012 at 2:16 am
Thanks a lot for the response. Yes I am picking SQLNCLI10 and not SQLNCLI11. I am able to see the tables and views once the connection is established, but I am not able to do the SELECT as it gives the MC DTC error.
The same SQL 2000 and SQL 2012 are working fine with SQL 2008 when we create linked server, so I dont think there is issue with MC DTC configuration... What you think might be the issue? Do you suggest that I should install SQl2008 and then upgrade to SQL2012 instead of installing a new copy?
July 13, 2012 at 7:00 pm
We had similar issues(same error) a few jobs ago when upgrading the OS and SQL same day. Maybe you did, or didnt also upgrade the OS, but our problem was the DTC co-ordinator. We dug and dug blaming sql until finally found an error with enough info to reveal MSDTC was the problem.
We added both machine names and tweaked DTC on both servers, doing nothing to sql at all, if it's enabled already maybe you need other changes. Each jump in versions has impending consequences and I've learned never discount cause something worked version to version especially in this case, being way apart.
July 13, 2012 at 7:21 pm
Koen Verbeeck (7/12/2012)
You have to make sure SQLNCLI10 is used, as SQLNCLI11 doesn't support connections to SQL Server 2000. Which provider did you choose in the Linked Server menu?
Koen, have you been able to get it to work?
Just curious since there is no upgrade path from 2000 to 2012 and 2000 compat mode is not even supported in 2012. That in mind makes me think that linked servers from 2012 down to 2000 are not supported either.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 16, 2012 at 6:59 am
SQLRNNR (7/13/2012)
Koen Verbeeck (7/12/2012)
You have to make sure SQLNCLI10 is used, as SQLNCLI11 doesn't support connections to SQL Server 2000. Which provider did you choose in the Linked Server menu?Koen, have you been able to get it to work?
Just curious since there is no upgrade path from 2000 to 2012 and 2000 compat mode is not even supported in 2012. That in mind makes me think that linked servers from 2012 down to 2000 are not supported either.
I'm not the OP, so I don't know 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2012 at 8:54 am
Thanks Guys for the response. I have not been able to fix this problem. I will try to tweak MSDTC today if that doesnt help... planning to instal SQL 2008 and then upgrade.
July 16, 2012 at 9:47 am
Koen Verbeeck (7/16/2012)
SQLRNNR (7/13/2012)
Koen Verbeeck (7/12/2012)
You have to make sure SQLNCLI10 is used, as SQLNCLI11 doesn't support connections to SQL Server 2000. Which provider did you choose in the Linked Server menu?Koen, have you been able to get it to work?
Just curious since there is no upgrade path from 2000 to 2012 and 2000 compat mode is not even supported in 2012. That in mind makes me think that linked servers from 2012 down to 2000 are not supported either.
I'm not the OP, so I don't know 🙂
I knew that.
I was curious if you had ever tried to create a linked server between 2k and 2k12 yourself and gotten it to work. 🙂
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 16, 2012 at 2:12 pm
Ah ok.
No I haven't created a linked server to SQL2000.
I replied to this thread originally because I know the 2012 native client is unable to connect to 2000.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 18, 2012 at 2:18 pm
I can also see the catalogs; however, when I run a select I get the same error:
Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
July 25, 2012 at 10:05 am
I have successfully created a linked server from SQL 2012 to a SQL 2000 server.
It isn't supported to the best of my knowledge but it has been working so far.
The notes I wrote up...
Since we are using a x64 bit 2012 server we need to add the DSN in the odbc32.exe as well as the x64 odbc.exe menu.
The odbc for 32 bit isn’t easy to find
C:\windows\System32
Right click and run as administrator, then system, then add...
Need to pick the non-version 11…it won’t work…pick the 6.01 version…in my example I had SQLSRV32.DLL (11/20/2010)
Then configure as you see fit (i.e. give it a name and point it at a server)
When an x64 bit connects to a 32 bit 2000 server you need to run the x64 to 32bit conversion script.
Instructions listed here
http://support.microsoft.com/kb/906954
Once you have that part done for both x64 ODBC and 32bit ODBC you can take the script below and configure as you like…hopefully it makes enough sense…
USE [master]
GO
EXEC sp_addlinkedserver
@server = '</Server name you put in the DSN/>', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = '</The actual server name/>', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=</The actual server name/>;UID= enter login;PWD=enter password;' -- ODBC Connection String.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'</Server name you put in the DSN/>',
@locallogin = N'enter a local login',
@useself = N'False',
@rmtuser = N'enter a remote login',
@rmtpassword = N'enter a remote password'
GO
Then when done and no errors...log into your 2012 server with your local login account and try a 4 part named query across...should work.
July 25, 2012 at 2:39 pm
Hi Leeland thanks a lot for detailed information...
July 25, 2012 at 3:36 pm
Sorry for the confusion...
In my example I configured both the 32 bit and x64 ODBC
the 32 bit ODBC.exe is located in that path I listed above C:\windows\System32 directory.
right click on the ODBC.exe run as admin and then create a new DSN (which will point to the 2000 server).
When you are configuring it asks what driver to use...I just listed the driver I used and tried to be descriptive.
When you configure obviously make note of the name of the DSN...because that is going to be the name of the linked server.
when you use the code I posted below you will replace the "</Server name you put in the DSN/>" with the name of the DSN you just created...
@datasrc = '</The actual server name/>',
you will replace that part with the actual server name
in this part you need to configure the login and password
@provstr = 'DRIVER={SQL Server};SERVER=</The actual server name/>;UID= enter login;PWD=enter password;' -- ODBC Connection String.
for example you could use the sa account and password
this code is for adding the login mapping on that particular linked server
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'</Server name you put in the DSN/>',
@locallogin = N'enter a local login',
@useself = N'False',
@rmtuser = N'enter a remote login',
@rmtpassword = N'enter a remote password'
GO
hope it helps...
July 26, 2012 at 3:01 pm
Leeland I get this error:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST2".
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...
EXEC sp_addlinkedserver
@server = 'TEST2', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = 'DSNTEST1', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=DSNTEST1;UID= TestUser;PWD=TEST;' -- ODBC Connection String.
July 26, 2012 at 4:41 pm
Laura_SqlNovice (7/26/2012)
Leeland I get this error:Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST2".
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...
EXEC sp_addlinkedserver
@server = 'TEST2', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = 'DSNTEST1', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=DSNTEST1;UID= TestUser;PWD=TEST;' -- ODBC Connection String.
You created the DSN both 32 bit and x64? (not sure if it was totally needed...I did it anyway)...
One other thing...you made them as System DSN's vs User DSN's correct?
When inside the Create New data source menu make sure you choose the 6.01.xxx version named "SQL Server"
You tested that account login and password on the 2000 server? case sensitive?
I seem to recall getting a similar error and I basically wiped everything off in regards to DSN's and Linked servers and started from scratch with the steps listed above...
I would say to start from square 1, won't take that long to go in and recreate the dsn's (are you using that account listed above when you are configuring the DSN?).
Then configure the script to add the linked server and then the linked server login...
let me know if it helps
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply