none
SqlTransaction has completed it is no longer usable error

    Question

  • Hi,

     

    I am getting this error in my application time to time but not able to recreate when I try to fix so need your help.

    I am using ado.net 2.0 and posting part of my code which will give some idea.

    This dll is used for parallel running more then one instance to insert data in to sql server database. when there is some other error in one instance like PK failed I have seen error "SqlTransaction has completed it is no longer usable error" coming from other instance when it should run just fine. if I restart same parallel process to recreate this error it runs fine even first instance has same error like PK failed. Please if you have time give feedback in this code - Thank you

     

    using System.Data.SqlClient;

     

    public int ExecuteTransformation(SqlObject transformation)
    {
     using (SqlConnection cn = new SqlConnection(transformation.SourceConnection))
     {
      cn.Open();
      ....
      SqlDataReader sdr = cmd.ExecuteReader();
      ....
      int i = ExecuteBlockTransformation(transformation, sdr);
      ....
      sdr.Close();   
     }
    }

     

    private static int ExecuteBlockTransformation(SqlObject transformation, SqlDataReader sdr)
    {
     try
            {
             connection = new SqlConnection(transformation.TargetConnection);
             connection.Open(); 
              ...
             using (SqlTransaction trans = connection.BeginTransaction())
             {
               ....
              try
              {
                SqlCommand command...
                 ...     
                     while (sdr.Read())
                     {
                        ...
                       Execute(transformation, trans, command, sql); 
                       ... 
                     }
              }
               catch (System.Exception ex)
                 {...}
               finally
               {
              
                  trans.Commit();
                            
                  sdr.Close();
               }

       }
     }
     catch (System.Exception ex)
     {...}
     finally
     {
              if (connection != null)
                      connection.Close();
     }
    }

    Friday, September 21, 2007 2:39 PM

Answers

  • Catching Exception is not a good idea.  You'll call Commit() on a connection that potentially has generated a transaction exception.
    Friday, September 21, 2007 5:51 PM
    Moderator
  • Then only catch the exception that a "failed row" throws, whatever exception type that might be.  Catching all exceptions, then committing the transaction is going to get you in the kind of trouble you're now having.
    Friday, September 21, 2007 8:15 PM
    Moderator

All replies

  • Catching Exception is not a good idea.  You'll call Commit() on a connection that potentially has generated a transaction exception.
    Friday, September 21, 2007 5:51 PM
    Moderator
  • finally

    {

    trans.Commit();

    sdr.Close();

    }

     

    I am catching exception to log failed rows. What should I check before Commit. How can I check in finally that transaction has exception, does "trans" set to null when there is error? and I should check

     

    if(trans != null)

    {

    trans.Commit();

    sdr.Close();

    }

    Friday, September 21, 2007 7:38 PM
  • Then only catch the exception that a "failed row" throws, whatever exception type that might be.  Catching all exceptions, then committing the transaction is going to get you in the kind of trouble you're now having.
    Friday, September 21, 2007 8:15 PM
    Moderator
  •  

    maybe you can commit as the final line in the try block...and rollback in the catch... Or maybe you might wanna move the try catch inside the  while loop so you can handle the rows one by one? (Just suggestions)
    Saturday, September 22, 2007 5:26 PM
  •  

    Thanks. I got this error again after running 5 good runs. I do have try block inside while loop sorry I did put that in my posting. 
     
    I put some console message to see which exception block it goes. When this error happen it goes to last catch block the one before connection.close()

    catch (System.Exception ex)
    {
    Log.FileLog("*****SqlTransaction Error Watcher in Exception block ******");
    ....
    }
    finally
    {
    if (connection != null)
    connection.Close();
    }

     

    When I check the Event Viewer it says : The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 8 time(s).

     

    in my application log file I get this

    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_summ_nav:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
    Message: execute gsp_upd_fct_acctg_from_sum_nav_pctg 'FINALIZED';
    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_summ_nav:This SqlTransaction has completed; it is no longer usable.
    Message:
    Message: 9/24/2007 3:01:19 PM: This SqlTransaction has completed; it is no longer usable.
    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_summ_nav completed...
    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_ytd_itd_rtrn_rt started...
    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_ytd_itd_rtrn_rt:A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
    Message: 9/24/2007 3:01:19 PM: fct_acctg_updt_ytd_itd_rtrn_rt completed...
    Message: 9/24/2007 3:01:20 PM: updt_summ_nav_all_fnd started...
    Message: 9/24/2007 3:01:24 PM: updt_summ_nav_all_fnd:A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
    Monday, September 24, 2007 7:53 PM
  • Not sure why you posted this, I hope you see you are catching exceptions that you shouldn't catch.  A "transport-level error" is a very serious failure and catching it will just lead to a chain of other exceptions.  There is no way you can commit a transaction after this, your log clearly shows this.

    There is no point in trying to do anything after your program is no longer able to talk to SQL Server.  You now know this because you are logging the exceptions.  Your user or her IT support tech would have no clue what is going on from the rapid succession of message boxes (if you'd choose to show one).  Or worse, have no clue why it just doesn't work because you're not telling that it failed.  You'll get a phone call from an angry customer once a week with "this *** just stopped working again, what happened?"  That's not good, especially considering that the real problem is not yours.

    Don't catch Exception.  Or sign a juicy support contract with your customer.
    Monday, September 24, 2007 9:09 PM
    Moderator
  • Thanks. I am catching all types exceptions just to log to log file. I understand in this case it's not helping any way because it is losing connection to database and my process stops. How can I investigate this issue is there any tool or code on system or database level I can use to find out why some time my sql server is dropping connection. I have SQL Server 2005 with SP2.

     

    Thank you - Ashok

    Monday, September 24, 2007 9:40 PM
  • No, the problem is that your process doesn't stop.  It should.  SQL server dropping a connection is not your problem.  It is your customer's problem, her LAN is flaky or she reboots the server once a day.  Make sure she sees cause and effect.
    Monday, September 24, 2007 10:03 PM
    Moderator
  • Check the disk space on the SQL server where the database and log files are located.
    Tuesday, June 01, 2010 6:06 AM
  • 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

    HydPhani

    Wednesday, May 15, 2013 2:24 PM