MSDTC error after upgrading to Sql 2005

Question

MSDTC error after upgrading to Sql 2005

sntiwary

Hall of Fame

Points: 3475
More actions
December 26, 2007 at 11:08 am

#154035

One of our application is failing with the below error after upgrading to SQl 2005. this was working in 2000. Please let me know if anyone is aware of such DTC issue.
Msg 3970, Level 16, State 2, Line 1
This operation conflicts with another pending operation on this transaction. The operation failed.
-SNT

Viewing 15 posts - 1 through 14 (of 14 total)

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

Sabrina D Nelson SSC-Addicted Points: 429 More actions · Answer 1

Have you checked the account that it is running under? I had several problems and found that the account was running under the local services account instead of the NT/Network Authority account. Once I changed this my server has worked just fine. Not sure if this is your problem or not because it is not exactly the same error; however, it may be part of the problem.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

alen teplitsky

SSC-Dedicated

Points: 30014

December 27, 2007 at 9:36 am

#763001

are you using XA drivers?

sntiwary Hall of Fame Points: 3475 More actions · Answer 3

thanks for the reply.

yes, I am using NT/Network Authority account

--SNT

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

if this is between 2 sql servers try to set DTC to run under a domain account that has SQL rights on each server

idiot auditors said we had to get rid of linked servers and use DTC for inter-SQL communication. morons go by some checklist that is years old and out of date

sntiwary Hall of Fame Points: 3475 More actions · Answer 5

sntiwary

Hall of Fame

Points: 3475

December 27, 2007 at 11:42 am

#763051

XA transaction is enabled in the MS DTC security

Balmukund SSCertifiable Points: 6648 More actions · Answer 6

1. what is the version on linked server? both are on same build(SQL 2005 SP2?)

2. Are you able to reproduce it? Do you have small sample?

3. Are you using Truncate table over linked server?

AndyO SSC Enthusiast Points: 169 More actions · Answer 7

I have the same error, below is a small sample to reproduce it (both servers are MS SQL 2005 SP2).

It looks like 'insert' and 'drop' operations joint together and I can't drop table after I've inserted new records to it.

Though it works on MS SQL 2000 SP4.

----------------------------------

set xact_abort on

begin tran

execute roinormalize.master.dbo.sp_executesql N'create table roinormalize.dbo.linkedtest (id int)'

GO

insert into roinormalize.roinormalize.dbo.linkedtest (id) select 1

execute roinormalize.master.dbo.sp_executesql N'drop table roinormalize.dbo.linkedtest'

commit tran

----------------------------------

sntiwary Hall of Fame Points: 3475 More actions · Answer 8

Hi Andy, Did you get any solution for this?

If you keep DDL and DML in seperate transaction then it works.

Please let me know if you have got any fix.

Thanks in advance

--SNT

AndyO SSC Enthusiast Points: 169 More actions · Answer 9

Sntiwary, I haven't found any solution yet.

As an alternative in my case I can pull data to the temp table on the linked server instead pushing it to the linked server from the source server. But it's not the best solution for me and I'm still looking for an alternative way.

Please let me know if you found any resolution.

sntiwary Hall of Fame Points: 3475 More actions · Answer 10

Hi Andy,

If you seperate your DDL and DML transaction then it works. Not sure if there is any fix for this post SP2 release since this was working in SQL 2000 and my current SQL server is 2005 with SP2

set xact_abort on

begin tran

execute roinormalize.master.dbo.sp_executesql N'create table roinormalize.dbo.linkedtest (id int)'

GO

commit tran

Begin tran

insert into roinormalize.roinormalize.dbo.linkedtest (id) select 1

Go

commit tran

Begin tran

execute roinormalize.master.dbo.sp_executesql N'drop table roinormalize.dbo.linkedtest'

commit tran

Thanks/SNT

sntiwary Hall of Fame Points: 3475 More actions · Answer 11

Hi Andy,

If you seperate your DDL and DML transaction then it works. Not sure if there is any fix for this post SP2 release since this was working in SQL 2000 and my current SQL server is 2005 with SP2

set xact_abort on

begin tran

execute roinormalize.master.dbo.sp_executesql N'create table roinormalize.dbo.linkedtest (id int)'

GO

commit tran

Begin tran

insert into roinormalize.roinormalize.dbo.linkedtest (id) select 1

Go

commit tran

Begin tran

execute roinormalize.master.dbo.sp_executesql N'drop table roinormalize.dbo.linkedtest'

commit tran

Thanks/SNT

AndyO SSC Enthusiast Points: 169 More actions · Answer 12

Hi, sntiwary !

Thanks for a suggestion but it doesn't solve the issue because in this case there is no point in transactions. I want to put inside transaction creation of a table, insert and drop operations. And it works for a local server.

For example:

begin tran

create table t (id int)

insert into t (id) values (1)

drop table t

commit tran

It works. But I can't make it work for a linked server.

sntiwary Hall of Fame Points: 3475 More actions · Answer 13

Hi Andy,

This is bug in sql 2005 and same thing has been resolved in SQL 2008

thanks

Shriniwas

sntiwary Hall of Fame Points: 3475 More actions · Answer 14

Hi Andy,

This is bug in SQL 2005 which has been fixed in SQL 2008.

Thanks

SNT