MS DTC has cancelled the distributed transaction

Question

MS DTC has cancelled the distributed transaction

kswathis

SSC-Addicted

Points: 476
More actions
November 20, 2007 at 3:07 pm

#118974

I'm trying to insert a row in one sql server using the insert trigger on the primary sql server.
Almost all the triggers are working,only two of them are giving me error saying that :
Msg 1206, Level 18, State 118, Procedure iContracts, Line 49
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
Can anyone help me in this regard...
Thanks,
S.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

My first guess would be that you hit a DTC timeout. This will cause DTC to cancel all of the related transactions. This may not be the same as your connection timeouts in your application and needs to be configured when you start your distributed transaction.

michaela SSCertifiable Points: 5649 More actions · Answer 2

This looks as a known sql 2k5 bug that has been solved after sp2; please take a look here http://support.microsoft.com/kb/937517, however there may be more than one reason you get the error.

Good luck

kswathis SSC-Addicted Points: 476 More actions · Answer 3

Micheal,

Thanks for the reply...I already tried in that way also...with NO LUCK 🙁

Do you have anything else in your mind which is causing this problem???

Anyone, Please help me in this regard....I really appreciate it!!!!!!!!!

Thanks,

S.

kswathis SSC-Addicted Points: 476 More actions · Answer 4

Ten Centuries---

I have seen the link that you have provided....but got a question ... The main server is on sql server 2005 SP2,but the remote (linked) server is on sql server SP1.

Is this the one causing me the problem??

Can you please let me know about this...have to solve it by early hours of tomorrow morning..

Thanks,

S.

michaela SSCertifiable Points: 5649 More actions · Answer 5

The fix should be applied on the instance you're running the distributed query from.

However, this is not the problem here; doing this from within a trigger is causing the hassle. Usually you do this with transactional replication. I'm not sure you have time for that, but if I would be you I would try to move that query from the insert trigger and find a workaround.

Good luck.

kswathis SSC-Addicted Points: 476 More actions · Answer 6

Michael,

Thanks for the immediate response...can you give me information about how to implement transactional replication for this case??

Actually I wrote this type of triggers on 19 tables, all are working fine except 2 of them...

thats why I'm worried..(Sp2 fix is on the primary database server only as you told..)..

Your help will be highly appreciated in tis regard..

Thanks,

S.

michaela SSCertifiable Points: 5649 More actions · Answer 7

This info is here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/5acf7585-fd64-4902-863b-f0132e0b478e.htm

kswathis SSC-Addicted Points: 476 More actions · Answer 8

kswathis

SSC-Addicted

Points: 476

November 20, 2007 at 7:51 pm

#752935

Thanks Michael,I'll try that

aparna-373073 SSC Rookie Points: 29 More actions · Answer 9

Hi,

I faced a similar problem. I was trying to select records from multiple tables and insert them . I used a cursor and it so happened that the one of the insert statements was wrongly built

eg

select * from table WHERE column1=1080 and column2= 'NULL' and column3= 'NULL'

I modified the procedure and it worked fine.

Hope it gives some idea to you