DTS Transaction Error

Question

Post reply

DTS Transaction Error

damcalcan

Old Hand

Points: 378
More actions
March 31, 2002 at 7:54 am

#105308

I’m using SQL Server 2000 Standard Edition SP 2.

I have a DTS package that consists of an “Execute SQL Task”. I want the “Execute SQL Task” to be executed in the context of a transaction so I ‘ve checked the following properties from Workflow Properties Dialog Box Options Tab : “Join transaction if present”; “Commit transaction on successful completion of this step” and “Rollback transaction on failure” . The DTS is stored in the above mentioned server .

Problem: When I execute the package from other machine than the one witch host the SQL Server I’ve got the following error :

Error Source : “MS OLEDB Provider for SQL Server”

Error Description : “Connection ‘SQL Server Conn’ for the task ‘DTS Task 1’ does not support joining distributed transaction or failed when attempted to join. New transaction cannot enlist in the specified transaction coordinator. ”

I have MDAC 2.7 on the client machine and the MSDTC started both on client and server machine.

When I execute the package from server machine everything works fine.

Thanks for any advice

calin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

danmorph SSC Veteran Points: 209 More actions · Answer 1

What Sql Server edition are you running on the non-server machine? If it is desktop, and the server is "per server" licence, you may be screwed. I had this problem the other day.

remedy: change the licence to "per seat" on the server, or install standard edition or better on the client machine.

However, be very careful with this, because you can only change the licence one way, and you can not return. I'm not sure whether the software can tell if you actually have the right kind of licence, either. This is an action that I read about, but was unwilling to risk, being ignorant of the full extent of the possible consequences.