DTC between SQL 2k and SQL 2k5

[xp8103]

Have set up a DTC relationship between a server running SQL2k and one running SQL2k5, in trying to fire off a trigger to update the 2k5 database, get the following:

"New transaction cannot enlist in the specified transaction coordinator"

The db firing the trigger is actually running in SQL7 compatibility mode.

 

[mrdenny]

DTC isn't actually a database thing, it's an OS thing, databases just use it more that just about anything else.

Are you running Windows 2000 or Windows 2003? The setup is a little different for each. Are there any firewalls between the servers (this will change the setup as well)?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[xp8103]

Yes, understood.

one server running Win2k in a clustered environment, one Win2k3 x64 ed.

 

[mrdenny]

OK, on the Windows 2003 server you need to install the networking part of DTC.

In Add/Remove Programs / Windows Components > Application Server > Enable network DTC access
Reboot if prompted
Open Start > Programs > Admin Tools > Component Services
Under Console Root > Component Services > Computers > My Computer right click on My Computer and select Properties
MS DTC tab
Security Configuration button
Check every check box, select the bottom radio button. Do not change the Windows Account listed.
OK all the way out and DTC will restart.

You should be able to run a distributed transaction between the machines. You can watch the Transaction Status under My Computer > Distributed Transaction Cooridenator.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[xp8103]

Just to clarify, all check boxes are checked and the "No Authentication Required" is the only radio button?

 

[mrdenny]

Yep.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog