October 17, 2016 at 8:06 pm
I am using VS Studio 2105 to create a console app and SSMS 2014 v 12.0.410.
I am running a VB.Net 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 the open and closing of the database:
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
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, Action\`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action\`1 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, TaskCompletionSource\`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 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 more complicated "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 & "
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
October 18, 2016 at 5:56 am
Wow. It's clear from the amount of detail included in your post that you've tried very hard to make this work. I feel your pain.
If you increase the polling interval, to say once every 2 minutes, does the problem go away? If yes, this would suggest that your issue is one of contention.
When you were designing the solution, did you consider building up the 105 proc calls in memory such that they needed to be submitted only once (via a data table or similar) rather than 105 times? As this is going to run so frequently, that might be worth exploring as it potentially speeds things up a lot.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 7:52 am
I see two possible issues here.
First (and most important), SQL is probably returning multiple errors, but you are only seeing the last one. When SQL does this, the first one is the error that actually says what happened, and the second one is pretty useless.
Add code to your catch block to get each item in the Errors collection of the SqlException object.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.errors(v=vs.110).aspx
If your stored procedures are using a TRY-CATCH and RAISERROR to raise the error to the application, then the first error is being lost in the stored procedure. There are two work-around for this issue 1) remove the try-catch (pre-SQL 2012) and 2) use THROW (with no parameters) in the CATCH block (SQL 2012 and later).
Second, I think your threads may be stepping on one another. It's hard to tell from the code you posted, so I may be wrong about this. It looks like your database object has a connection as an instance variable. Is the database object a static class or does each thread instantiate its own database object? The threads should not share a connection. Whenever an object crosses thread boundaries, there should be locking around it. In the case of a SqlConnection, there is no reason for threads to share one. The SQL client uses connection pooling by default, so when you call Connection.Open() you aren't necessarily opening a new connection, just pulling an open one out of the pool. Again, I can't see enough of your code to be certain about this.
October 18, 2016 at 1:06 pm
Phil,
Yes, I tried increasing it to 2 minutes. But it still fails after the 3rd poll.
Yes, 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.
October 18, 2016 at 1:21 pm
dc4444 (10/18/2016)
Phil,Yes, I tried increasing it to 2 minutes. But it still fails after the 3rd poll.
Yes, 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.
Interesting.
If it were me, I think I would be stepping through the code interactively (ie, a debug session) and watching it fail. Before the failure happens, check the values of all the locals and make sure that they are as expected.
Keep an eye open for anything unexpected. Make sure that all of your objects are being released/disposed as expected between runs. And try to get some better error info back from the SQL Server call, if possible.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 1:23 pm
Stephanie,
1.) I added the code to loop threw 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.
2.) The insert stored procedure is not using TRY-CATCH or RAISERROR. It's pretty simple.
Note: I have since modified it last night to do set processing passing in a 'table valued parameter'. But that did not stop it from failing.
CREATE PROCEDURE [dbo].[InsertBandwidthLogTest6]
@SwitchIPvarchar(max),
@PortIndexint,
@PortSpeedbigint,
@InOctetsbigint,
@OutOctetsbigint,
@TimeStampdatetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SwitchIDint
SELECT @SwitchID = SwitchID
FROM Switch
WHERE SwitchIP = @SwitchIP
INSERT INTO BandwidthLogTest6 (
SwitchID,
PortIndex,
PortSpeed,
InOctets,
OutOctets,
TimeStamp )
VALUES (
@SwitchID,
@PortIndex,
@PortSpeed,
@InOctets,
@OutOctets,
@TimeStamp )
END
3.) I have a database connection class with functions I call to open and close.
Currently, I use it for the main process (the insert) as well as the thread process.
The current 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 the open and closing of the database:
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
I too thought the sharing of the database connection might be an issue, so I did create a separate connection string and a set of open and close function that I used for the thread process. But that did not stop it from failing.
October 18, 2016 at 1:28 pm
While it will probably have little effect, I would suggest reducing the varchar(max) column to something more realistic.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 4:29 pm
Googling this error gives widely varying results. There are different kinds of scenarios that cause it, some of which are fixed by cumulative updates.
I suspect this is SQL Server's way of saying "that internal thing should not have happened." There probably won't be any obvious bugs in your code that cause this.
So, try restarting SQL Server, running against a different server (if possible), apply cumulative updates (if missing), and if none of that works, remove bits of the functional code one at a time until it doesn't throw the error.
October 19, 2016 at 9:11 am
Looks like their is an update missing. We will apply that and see if that resolves it. Will let you know. Thanks for all the suggestions.
October 21, 2016 at 8:42 am
We ran a Windows update and did a reboot and it fixed the problem. Thanks to all though for your input.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply