This SqlTransaction has completed it is no longer usable configuration error

Question

I ve been working on this for about a day and a half now  and searched numberous blogs and help articles on the Web   I found several questions on SO related to this error  but I didn t think they quite applied to my situation  or in some cases  unfortunately  I couldn t understand them well enough to implement  P    I m not sure I can describe this well enough for help    but here goes   We have a  NET app to track our resources   There s an export function to copy a resource to the time tracking system and the billing system  this accesses a stored procedure that links to the time and billing databases   I recently moved the billing system database to a new server  original server  Server 2003 SP2  SQL 2005  new server  Server 2008 R2  SQL 2008 R2    I have a Linked Server set up that points to the 2008 databases   I updated the stored procedure to point to the 2008 server  and then I got an error about MSDTC and RPC  http   www safnet com writing tech archives 2007 06 server myserver html    I enabled  rpc rpc out  on the Linked Server and set MSDTC to allow Network Access  something like this  http   www sqlwebpedia com content msdtc-troubleshooting    Now I m getting the above  when I try to run the export function   This SqlTransaction has completed  it is no longer usable   What seems odd to me is that when I just run the stored procedure  from SSMS   it says it completes successfully     Has anyone seen this before   Have I missed something in the configuration   I keep going over the same pages  and the only thing I found was that I didn t reboot after making the MSDTC changes  mentioned in here  http   social msdn microsoft com forums en-US adodotnetdataproviders thread 7172223f-acbe-4472-8cdf-feec80fd2e64     I can post part or all of the stored procedure  if it would help    please let me know

User · Answer

Here is a way to detect Zombie transaction

SqlTransaction trans = connection.BeginTransaction();

//some db calls here

if (trans.Connection != null) //Detecting zombie transaction
{
  trans.Commit();
}

Decompiling the SqlTransaction class, you will see the following

public SqlConnection Connection
{
  get
  {
    if (this.IsZombied)
      return (SqlConnection) null;
    return this._connection;
  }
}

I notice if the connection is closed, the transOP will become zombie, thus cannot Commit. For my case, it is because I have the Commit() inside a finally block, while the connection was in the try block. This arrangement is causing the connection to be disposed and garbage collected. The solution was to put Commit inside the try block instead.

User · Answer

Also check for any long running processes executed from your  NET app against the DB  For example you may be calling a stored procedure or query which does not have enough time to finish which can show in your logs as    Execution Timeout Expired  The timeout period elapsed prior to completion of the operation or the server is not responding    This SqlTransaction has completed  it is no longer usable     Check the command timeout settings Try to run a trace  profiler  and see what is happening on the DB side

User · Answer

In my case the problem was that one of the queries included in the transaction was raising an exception  and even though the exception was  gracefully  handled  it still managed to roll back the entire transaction   My pseudo-code was like   var transaction   connection BeginTransaction    for all the lines in a file         try           InsertLineInTable       INSERT statement might fail and throw an exception             catch               notify the user about the error on line x and continue              Commit and Rollback will fail if one of the queries     in InsertLineInTable threw an exception if CheckTableForErrors          transaction Commit      else       transaction Rollback

User · Answer

I have the same problem  This error occurs because conection pooling   When exists two or more users acess the system the connetion pooling reuse a connetion and the transation too  If the first user execute commit ou rollback the transaction is no longe usable

User · Answer

Had the exact same problem and just could not find the right solution  Hope this helps somebody  I have an  NET Core 3 1 WebApi with EF Core  Upon receiving multiple calls at the same time  the applications was trying to add and save changes to the database at the same time  In my case the problem was that the table that the data would be saved in did not have a primary key set  Somehow EF Core missed when the migration was ran from the application that the ID in the model was supposed to be a primary key  I found the problem by opening the SQL Profiler and seeing that all transactions was successfully submitted to the database  from the application  but only one new row was created  The profiler also showed that some type of deadlock was happening but I couldn t see much more in the trace logs of the profiler  On further inspection I noticed that the primary key identifier was missing on the column  quot Id quot   The exceptions I got from my application was   This SqlTransaction has completed  it is no longer usable   and or  An exception has been raised that is likely due to a transient failure  Consider enabling transient error resiliency by adding  EnableRetryOnFailure    to the  UseSqlServer  call

User · Answer

I had this recently after refactoring in a new connection manager   A new routine accepted a transaction so it could be run as part of a batch  problem was with a using block   public IEnumerable lt T gt  Query lt T gt  IDbTransaction transaction  string command  dynamic param   null      using  transaction Connection          using  transaction              return transaction Connection Query lt T gt  command  new DynamicParameters param   transaction  commandType  CommandType StoredProcedure                 It looks as though the outer using was closing the underlying connection thus any attempts to commit or rollback the transaction threw up the message  This SqlTransaction has completed  it is no longer usable    I removed the usings added a covering test and the problem went away   public IEnumerable lt T gt  Query lt T gt  IDbTransaction transaction  string command  dynamic param   null      return transaction Connection Query lt T gt  command  new DynamicParameters param   transaction  commandType  CommandType StoredProcedure       Check for anything that might be closing the connection while inside the context of a transaction

User · Answer

I believe this error message is due to a  zombie transaction    Look for possible areas where the transacton is being committed twice  or rolled back twice  or rolled back and committed  etc     Does the  Net code commit the transaction after the SP has already committed it   Does the  Net code roll it back on encountering an error  then attempt to roll it back again in a catch  or finally  clause   It s possible an error condition was never being hit on the old server  and thus the faulty  double rollback  code was never hit   Maybe now you have a situation where there is some configuration error on the new server  and now the faulty code is getting hit via exception handling   Can you debug into the error code   Do you have a stack trace

User · Answer

In my case   I ve some codes  which needs to execute after committing the transaction at the same try catch block One of the code threw  an error  then try block handed over the error to it s catch block which contains the transaction rollback  It will show the similar error  For example look at the code structure below    SqlTransaction trans   null   try   trans   Con BeginTransaction       your codes    trans Commit      your codes having errors    catch Exception ex         trans Rollback      transaction roll back        error message    finally           connection close     Hope it will  someone

User · Answer

I have recently ran across similar situation  To debug in any VS IDE version  open exceptions from Debug  Ctrl   D  E  - check all checkboxes against the column  Thrown   and run the application in debug mode  I have realized that one of the tables was not imported properly in the new database  so internal Sql Exception was killing the connection  thus results into this error    Gist of the story is  If Previously working code returns this error on a new database  this could be database schema missing issue  realize by above debugging tip    Hope It Helps   HydTechie

User · Answer

For what it s worth  I ve run into this on what was previously working code   I had added SELECT statements in  a trigger for debug testing and forgot to remove them   Entity Framework   MVC doesnt play nice when other stuff is output to the  quot grid quot    Make sure to check for any rogue queries and remove them

[sql] "This SqlTransaction has completed; it is no longer usable."... configuration error?

Examples related to sql

Examples related to stored-procedures

Examples related to sql-server-2008-r2

Examples related to server-error