Our system started to get intermittent errors like the foolowing in the code that was NOT changed recently:
System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
I was pointed to MS Kb http://support.microsoft.com/kb/912732, that I’ve installed, but it didn’t change -errors still appeared.
Unfortunately, the KB doesn’t have a list of changed files and their versions, so I can’t say for sure that my install was successful.
The same issue reported in different places: http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic21204.aspx , http://support.citrix.com/forums/thread.jspa?forumID=115&threadID=93236 ;http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic37219.aspx
In the thread SQL Server Raiserror and .NET SqlDataAdapter noted ,that the error ould be result of extra commit/Rollbcks in SQL trigger that uses @@ROWCOUNT > 0 .
Some people(see here) had this problem back in 2003 and MS dona fix for them. Why we have the same errors now?
The post “SQL Server, ADO.NET and Nested Transactions” points that SQL Server setting SET XACT_ABORT ON can cause the errors.
But the setting of SET XACT_ABORT is by default OFF and can be set at execute or run time , can’t be specified on Server/Database level.
The MSDN SqlTransaction.Rollback Method () documentation has a good example , showing that in a catch block, error should be logged first, and then call Rollback in another Try block.(see also this Microsoft Knowledgebase entry).
Finally I understood that Rollback is NOT required when using block is specified, because “The transaction is rolled back in the event it is disposed before Commit or Rollback is called.”
It is confirmed in Community Content in http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx and in CodeProject Easier Database Transactions – Extending the Using Statement to Perform Automatic Database Transactions,
We will need to change our code to get rid of Rollback() in catch sections,when using block is specified.It will help to see the actual error causing exception.
Update: After removing explicit calls to Rollback we found that tte most of errors were caused by timeouts in access to database. It is still required to solve these errors, but it is another story.