Distributed transaction between 2005 & 2000 failing
-
Hi Folks,
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-Server\TServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-Server\TServer" was unable to begin a distributed transaction.
And the other way round:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Have you searched this site? I think there may be a couple of threads that discuss this. Let me know if you find anything, as I need to look through some notes I have, as we had a similiar issue when we upgraded several of our servers to SQL Server 2005.
-
I am not entirely sure this was "the exact" same problem I faced a while ago but I am sure I had to apply instcat.sql to the 2000 server.
* Noel -
I have been searching this site, MSDN and google for days now with this issue. There is nothing I can find that I have not yet tried.
Noel: I have already run instcat.sql on the 2000 box. Till then, even post SP4, a normal linked server qury would not work.
Update: It works.... How? I don't really know (Which is worse than it not working)
One change I have made is that I deleted the MSDTC recourse from the Quorum resource and added it to the SQL resources (on the S2K cluster) This seemed to do the trick. Brought DTC online, took SQL off and back on and vola - it worked.... Now this contradicts everything I have read were people leave it on the Quorum without a hassle.
I'm about to move it back to the Quorum and see...
Though it's working, I don't know what / how which will cause me headache once it is live and stops working...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Ok, if I put DTC in the cluster group, it will not work. if I put it in the SQL group, it works.
While it is ok that it works, I would rather not have it in the SQL group as there are 3 other nodes / instances to this cluster. if I bring this one resource offline, I kill everything that uses DTC.
I will try setting up it's own resource group and see although, I have little hope...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply