none
A transport-level error - An existing connection was forcibly closed by the remote host

    Question

  • Getting the following error on code that reads a record using a DataReader, then updates fields in the record.  The error can be reproduced but does not happen with all data records.  The trace mentions a timeout...???

    System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException (0x80131904): 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.)
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
    at System.Data.SqlClient.TdsParserStateObject.WriteSni()
    at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
    at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
    at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Blue_Ribbon_2011_ASPX.Admin.stripoutword.CleanButton_Click(Object sender, EventArgs e) in C:\Users\jparsons\Documents\Visual Studio 2008\Projects\BlueRibbon2011 ASPX\Blue Ribbon 2011 ASPX\Admin\stripoutword.aspx.cs:line 71
    at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
    at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    at System.Web.UI.Page.HandleError(Exception e)
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    at System.Web.UI.Page.ProcessRequest()
    at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
    at System.Web.UI.Page.ProcessRequest(HttpContext context)
    at ASP.admin_stripoutword_aspx.ProcessRequest(HttpContext context) in c:\Users\jparsons\AppData\Local\Temp\Temporary ASP.NET Files\root\ca1f2daf\bc56cecf\App_Web_wlrrxrsn.10.cs:line 0
    at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
    at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    Code snippet:

    SqlConnection Conn2 = new SqlConnection(ConnStr);
    Conn2.Open();
    SqlCommand MainSelectCommand = new SqlCommand("SELECT * FROM tbl_main WITH (NOLOCK) WHERE school_id = '" + SchlReader["school_id"] + "' ;", Conn2);
    SqlDataReader MainReader = MainSelectCommand.ExecuteReader();
    if (MainReader.HasRows)
        {
        MainReader.Read();
        SqlConnection Conn2A = new SqlConnection(ConnStr);
        Conn2A.Open();
        SqlCommand MainUpdateCommand = new SqlCommand("UPDATE tbl_main SET qii_3='" + CommonClass.CleanUpWord(MainReader["qii_3"]) + "' WHERE school_id = '" + SchlReader["school_id"] + "';", Conn2A);
        MainUpdateCommand.ExecuteNonQuery();
        Conn2A.Close();
        SqlConnection Conn3 = new SqlConnection(ConnStr);
        Conn3.Open();
        SqlCommand MainUpdateCommand1 = new SqlCommand("UPDATE tbl_main SET qii_8_languages='" + CommonClass.CleanUpWord(MainReader["qii_8_languages"]) + "' WHERE school_id = '" + SchlReader["school_id"] + "';", Conn3);
        MainUpdateCommand1.ExecuteNonQuery();
        Conn3.Close();
     ...repeat for 20 updates ...
    ..the data being updated is string data that can be 500+ words long ...

    This fails with some data records at random update statements.  Once it fails on a data record it always fails at the same spot.  Ideas are welcome...I am at a wall.


    JP

    Thursday, March 08, 2012 7:00 PM

Answers

  • Tried the stored procedure approach with the same result.

    The problem had something to do with the amount of data being updated and concurrency (the NOLOCK).  I still don't know what caused it, but reading all the data, closing the read connection, and then doing a single update fixed it.


    JP

    • Marked as answer by BusyExec Thursday, March 15, 2012 2:18 PM
    Thursday, March 15, 2012 2:18 PM

All replies

  • Did you check to see if the SQL Server was available when you received the error? or does it happen even when Server is up and running?
    Friday, March 09, 2012 1:28 AM
  • You should really do some research on how to use Stored Procedures.  Calling a database 3 times just to do simple update is crazy and will cause you nothing but pain.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    • Marked as answer by KJian_ Thursday, March 15, 2012 5:41 AM
    • Unmarked as answer by BusyExec Thursday, March 15, 2012 2:14 PM
    Friday, March 09, 2012 4:51 AM
  • Tried the stored procedure approach with the same result.

    The problem had something to do with the amount of data being updated and concurrency (the NOLOCK).  I still don't know what caused it, but reading all the data, closing the read connection, and then doing a single update fixed it.


    JP

    • Marked as answer by BusyExec Thursday, March 15, 2012 2:18 PM
    Thursday, March 15, 2012 2:18 PM