Unable to enlist in transaction

[millross]

Hi there,

I have a DTS package which is setup to join a transaction if present (on all package steps) and to rollback on failure (again on all package steps)

I am trying to run this DTS package with a set of Data transformation tasks, between a Windows 2000 server/Sql Server 2000 and another Windows 2000 server/Sql Server 2000.

Both machines have MSDTC service started.

When I try and run the DTS package using the designer, or DTSRunUI (With Global vars populated) the very first item fails and states "Unable to enlist in transaction" - "New transaction cannot enlist in the specified transaction coordinator".

I have found numerous threads based on tweaking Windows Server 2003 to fix this, but nothing for Windows 2000.

I'm really a .NET developer and not a DBA, so im not sure where to look next.

Any help greatly appreciated!

Matt

 

[mrdenny]

Start by restarting MSDTC on both machines.

Can you make a distributed transaction from one machine to another?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[millross]

ok, i will restart the services on both machines.

is there a quick way to make a distributed transaction, to test that it's working?

 

[mrdenny]

This will test that MSDTC is working correctly on both SQL Servers.

begin distributed transaction
select *
from remoteserver.master.dbo.sysfiles
commit

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[millross]

Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'PNSQL01\DEV' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Is the response!

Question) Does DTC only work with Linked servers? If they dont have to be linked for it to work, is it a good idea to do so anyway?

 

[mrdenny]

No they don't have to be linked for MSDTC to work. However if you link then it is an easy way to check that MSDTC is working.

What happens if you setup the links and try again?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com