Error: Transaction context in use by another session

I am programming in ASP and SQL server.

I am using this tag <%Transaction=Required%> to do the transaction.

And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...

It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.

I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.

Could you please send the code of your transaction ?

Also, are you gettign GetContext Transaction ?

Regards-

|||

Actually, the transaction is done only by using this tag <%@. Language=VBScript Transaction=Required%> in the begnning of the code, and the MTS (microsoft transaction server) will work the transaction.

It works well... if I get any error in the middle of the code everything is cancelled. And if take this tag out of my code then there will be no transaction.

It looks to me that the problem is not in the code. It happened that even when I don't have any insert,update or delete I get this message. It seems that this "MTS" opens two sessions to execute the queries... I would like to know how could I use that to avoid this problem ? Is there any configuration needed, either in SQL Server or this MTS ?

Error: The connection manager failed to defect from the transaction.

Does anybody know what it means?

The context is: a package with a single Data Flow finishes with this error: [Connection manager "xxx"] Error: The connection manager failed to defect from the transaction.

All components inside the data flow reports a successfull green status, however the DataFlow Task remains yellow for a long time, when suddenly the package stops with the above message. Another information is that the table remains locked during package execution (a select on that table does not complete...). This is a serious issue, and I couldnt find any relevant information about that. Thanks in advance.

Hi Half Abude,

Can you tell us more about the package? What kind of SQL is being executed? Also I'm just guessing you're using transactions in the package, can you tell us how you've set the transaction boundaries in the package? (i.e. on what object(s) have you set TransactionOption to Required?)

Thanks, -David