SQL error: A severe error occurred on the current command. The results, if any, should be discarded

Transact-SQL
1

I am using VS Studio 2105 / SSMS 2014 v 12.0.410. Using VB.net.

I am running a console app that does polling on the 0 and 30 second mark. It will do this 24 by 7. As part of the process, a background thread is spawned. (See explanation below).

The database connection string is:

add key="ConnectionString" value="Server=xxxxx,14334;Database=xxx_dev;Uid=xxx_dev;Password=xxxxxx;MultipleActiveResultSets=true"

The class and its functions called for open and close:

Dim strConnectionString As String = ConfigurationManager.AppSettings("ConnectionString")

Sub OpenDB()
    If objConn.State = Data.ConnectionState.Open Then
        objConn.Close()
    End If

    objConn.ConnectionString = strConnectionString
    objConn.Open()
    objCmd.Connection = objConn

    objCmd.CommandType = Data.CommandType.Text
End Sub

Sub CloseDB()
    objConn.Close()
End Sub

enter image description here

I customized the error message to get all that I could about it.

A severe error occurred on the current command. The results, if any, should be discarded. --> State: 0 --> Source: .Net SqlClient Data Provider --> Error number: 0 --> Line number: 0 --> Line number: -2146232060 --> Class: 11 --> Procedure: --> Call stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ReliableSiteBandwidthPollingTester.Module1.SaveBandwidthPollingLog(Switch SaveSwitch, String& strMessage) in C:\Dans\Work 2\Working Area\Switch Polling - design 3\Tester apps\ReliableSiteBandwidthPollingTester\ReliableSiteBandwidthPollingTester\Module1.vb:line 427

The polling process:

It interrogates a network switch gathering data. After the interrogation, it then executes a simple stored procedure 105 times which inserts a row into a table each time. It then starts a background thread which executes a "calculation process" stored procedure that uses the table just inserted into as well as other tables. It does inserts into another table - and potentially deletes. A transaction is used. It either commits or rolls back. However, I believe that the procedure does not finish before the next poll begins - the next 30 second mark.

The simple insert stored procedure has been tested stand alone many times and works fine every time.

The "calculation process" stored procedure has been tested stand alone many times and works fine every time.

I start the console app and it it polls 3 times and the fails with that error. Before failing, it writes the out 3 sets of 105 rows to the table. It also inserts 290 rows into another table as part of the process.

   Sub PollSwitch(sender As Object, e As ElapsedEventArgs)

    Dim strMessage As String = ""
    Dim strPollError As String = ""
    Dim bPollResult As Boolean
    Dim NetworkSwitch As Switch

    ' This is the format to display as it is how it is stored in the info log table.
    Dim dtFormat As String = "yyyy-MM-dd hh:mm:ss.fff"

    If bErrorInThread = True Then
        ' There was an error in the background thread during the previous issuance of the thread. So do not continue.
        strMessage = "Critical Error - in the background thread. See the 'BandwidthInfoLog' table. Refer to this log date: " + dtThreadStartDateTime.ToString(dtFormat)
    End If

    If strMessage = "" Then
        ' Create a new instance.
        NetworkSwitch = New Switch(strCommandLineSwitchIP, strCommandLineCommunityString)

        Try
            ' Do the switch polling.
            bPollResult = NetworkSwitch.Poll(strPollError)

            If bPollResult = False Then
                strMessage = "Warning - in bandwidth poll. Poll error at " & Now & ": " & strPollError & " Poll will continue."
            Else
                ' Save to the bandwidth polling log.
                SaveBandwidthPollingLog(NetworkSwitch, strMessage)

                If InStr(strMessage, "Critical") = 0 Then
                    Console.WriteLine("Successfully polled at " & Now() & ".")

                    ' Set the date/time as it will be used above in an error message should the thread process fail.
                    dtThreadStartDateTime = Now()

                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    ' Now do the "calculation process" - it will be in it's own background thread.
                    ' Note: the thread ends when the ProcessCalculatedSwitchPolling method ends.
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    Dim thread As New Thread(AddressOf ProcessCalculatedSwitchPolling)
                    thread.Start()
                End If
            End If
        Catch ex As Exception
            strMessage = "Critical Error - in bandwidth poll - " & ex.Message & "<br /><br />Switch: " & strCommandLineSwitchIP
        End Try
    End If

    If InStr(strMessage, "Critical") > 0 Then
        ' Stop this method (this poll timer) as there is a critical error.
        DisposeTimer()

        ' Show message.
        Console.WriteLine(strMessage)
        Console.WriteLine("--------->>>> PRESS ENTER TO QUIT.")
        Console.ReadKey()

        ' Exit the console application here as there is a critical error.
        ' Normally the exit would occur in the main method when the User hits a key.
        Environment.Exit(0)
    End If
End Sub

The call stack shows it fails on line 427 which is the .ExecuteNonQuery() line.

   Sub SaveBandwidthPollingLog(ByVal SaveSwitch As Switch, ByRef strMessage As String)
    Const strFunctionId As String = "VB - savebandwidthpollinglog. Error ID: "
    Const iSQLErrorId As Integer = 501
    Const iCATCHErrorId As Integer = 502

    Dim dtCurrentDateTime As Date = Now()
    Dim strProcessInfoLogResult As String = ""
    Dim strAdditionalInfoForLog As String = ""

    ' This is the format to display as it is how it is stored in the info log table.
    Dim dtFormat As String = "yyyy-MM-dd hh:mm:ss.fff"

    DBFunc.OpenDB()

    Try
        With DBFunc.objCmd
            .CommandType = Data.CommandType.StoredProcedure
            .CommandText = "InsertBandwidthLogTest6"
            ' Note: it was timing out, so the SqlCommand.CommandTimeout property has expired; the default timeout is 30 seconds.
            .CommandTimeout = 0

            For Each SavePort In SaveSwitch.Port
                .Parameters.Clear()
                .Parameters.AddWithValue("@SwitchIP", SaveSwitch.IP)
                .Parameters.AddWithValue("@PortIndex", SavePort.Index)
                .Parameters.AddWithValue("@PortSpeed", SavePort.Speed)
                .Parameters.AddWithValue("@InOctets", SavePort.InOctets)
                .Parameters.AddWithValue("@OutOctets", SavePort.OutOctets)
                .Parameters.AddWithValue("@TimeStamp", SavePort.TimeStamp)

                .ExecuteNonQuery()
            Next
        End With
    Catch sqlex As SqlException
        ' Its a critical issue.
        If InStr(sqlex.Message, "Critical") > 0 Then
            ' Coming from the stored procedure.
            strMessage = sqlex.Message
        Else
            strProcessInfoLogResult = ProcessInfoLog(dtCurrentDateTime, sqlex.Message + " --> State: " + sqlex.State.ToString() + " --> Source: " + sqlex.Source + " --> Error number: " + sqlex.Number.ToString() + " --> Line number: " + sqlex.LineNumber.ToString() + " --> Line number: " + sqlex.HResult.ToString() + " --> Class: " + sqlex.Class.ToString() + " --> Procedure: " + sqlex.Procedure + " --> Call stack: " + sqlex.StackTrace, strAdditionalInfoForLog, strFunctionId, iSQLErrorId)
        End If
    Catch ex As Exception
        strProcessInfoLogResult = ProcessInfoLog(dtCurrentDateTime, ex.Message, strAdditionalInfoForLog, strFunctionId, iCATCHErrorId)
    Finally
        ' Close database.
        DBFunc.CloseDB()
    End Try
End Sub

The Thread process:

    Public Sub ProcessCalculatedSwitchPolling()
    Const strFunctionId As String = "VB - processcalculatedswitchpolling. Error ID: "
    Const iSQLErrorId As Integer = 601
    Const iCATCHErrorId As Integer = 602

    Dim strProcessInfoLogResult As String = ""
    Dim strAdditionalInfoForLog As String = ""
    Dim strInputParms As String = ""

    ' Set for the error log.
    strInputParms = "S/P parmameters - switch IP Address: " & strCommandLineSwitchIP

    DBFunc.OpenDB()

    Try
        With DBFunc.objCmd
            .CommandType = Data.CommandType.StoredProcedure
            .CommandText = "ProcessBandwidthLogCalculatedTest6"
            ' Note: it was timing out, so the SqlCommand.CommandTimeout property has expired; the default timeout is 30 seconds.
            .CommandTimeout = 0
            .Parameters.Clear()

            .Parameters.AddWithValue("@SwitchIP", strCommandLineSwitchIP)

            .ExecuteNonQuery()
        End With
    Catch sqlex As SqlException
        ' Its a critical issue.
        If InStr(sqlex.Message, "Critical") > 0 Then
            ' Coming from the stored procedure.
            ' Set the global variable - the thread error indicator.
            bErrorInThread = True
        Else
            ' Not coming from the stored procedure.
            bErrorInThread = True
            ' Log the exception as it was not logged in the stored procedure.
            strAdditionalInfoForLog = strInputParms
            strProcessInfoLogResult = ProcessInfoLog(dtThreadStartDateTime, sqlex.Message, strAdditionalInfoForLog, strFunctionId, iSQLErrorId)
        End If
    Catch ex As Exception
        bErrorInThread = True
        ' Log the exception.
        strAdditionalInfoForLog = strInputParms
        strProcessInfoLogResult = ProcessInfoLog(dtThreadStartDateTime, ex.Message, strAdditionalInfoForLog, strFunctionId, iCATCHErrorId)
    Finally
        ' Close database.
        DBFunc.CloseDB()
    End Try
End Sub
2

That, repeated calling of an Sproc to insert a single row each time, will be relatively slow. It would perform much better if the Sproc was called once and it inserted a "block" of data, in a single statement. i.e. Set-based rather than row-by-row. Dunno if that is related to your problem, but you did say "I believe that the procedure does not finish before the next poll begins"

I would be interested in how long the SProc was taking - e.g. by putting a DEBUG statement in your code to record the start / end time of:

    .ExecuteNonQuery()

maybe there is something else going on that is taking a long time - such as a Trigger on the table that the row is being inserted into, or update of statistics, or slow index updates. If you cannot log that time in you application you could do it in the SProc - create a temporary table in your database with columns for Date/Time and Action and insert a row at the start, and end, of your Sproc with the name of the SProc and "Start" / "End" description.

3

Kristen,

1.) I redesigned the logic so that it uses a "table valued parameter" passed to the insert stored procedure so that it does the 1 call as "set processing" as opposed to the 105 separate calls which were doing row-by-row processing. But it still fails after the 3rd poll. NOte this is where the call stack indicates as theline it is failing on - the .ExecuteNonQuery().

2.) I increased the poll to 2 minutes giving the simple insert stored procedure and the thread process (which calls a more complicated procedure) more time to complete. But it still fails after the 3rd poll.

3.) There are no triggers, No indexes.

4.) I dumped - along with the call stack - the SqlErrors Collection.
But all I get is: Index #: 0 Error: System.Data.SqlClient.SqlError: A severe error occurred on the current command. The results, if any, should be discarded. Basically the same as the original minus the call stack that I had included.

I have yet to set up the logging the start and end times.

4

Does it always fail after the 3rd poll (it appears that way in ALL the tests you have described, with all the changes you have made. If that's the case then there must be something about that 3rd poll.

Is it definitely failing the 3rd poll IN the SQL command? no chance there is some side-effect from the polling which is making it look like the error is in the SQL?

SQL Profiler will show you what SQL commands are actually being sent to SQL to execute.