Simple Console Application SQL Update Query - Timing Out

Question

I have the following VB.Net console application subroutine, it reads emails from a table and attempts to send them, if it sends them successfully it will then update the same database as sent with the time.

This code was working fine, however I am now receiving the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I have tried running the SQL script separately in management studio and it works fine.

I have also tried verifying the server space and database size, both are fine.

Can anyone help with a solution?

Connection(True)
    Using comSQL As New SqlCommand("SELECT * FROM Seq_Emails_ToSend", conSQL)
        Using dr As SqlDataReader = comSQL.ExecuteReader
            While dr.Read

                Dim madTO As MailAddressCollection = ConvertStringtoMAD(dr("Email_TO").ToString)
                Dim madCC As MailAddressCollection = ConvertStringtoMAD(dr("Email_CC").ToString)
                Dim madBCC As MailAddressCollection = ConvertStringtoMAD(dr("Email_BCC").ToString)
                Dim isHTML As Boolean = CBool(dr("Email_HTML").ToString)
                Dim Priority As MailPriority = CInt(dr("Email_Priority").ToString)
                Dim Subject As String = CStr(dr("Email_Subject").ToString)
                Dim Body As String = CStr(dr("Email_Body").ToString)
                Dim ID As Integer = CInt(dr("Email_ID").ToString)
                Dim Status As String

                If EmailFile(madTO, madCC, madBCC, "", isHTML, Priority, Subject, Body) Then
                    Status = "Sent"
                Else
                    Status = "Failed"
                End If

                Using comSQL2 As New SqlCommand("UPDATE Seq_Emails SET [Status] = @Status, [Date_Sent] = @Date WHERE Email_ID = @ID", conSQL)
                    comSQL2.Parameters.Add("@ID", SqlDbType.Int).Value = ID
                    comSQL2.Parameters.Add("@Status", SqlDbType.NVarChar).Value = Status
                    comSQL2.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now

                    comSQL2.ExecuteNonQuery()
                End Using

            End While
        End Using
    End Using
    Connection(False)

Hi Sorry, I do not know what MARS is or how to enable it? However I know I haven't disabled it and the above code was working yesterday and has been for the last few months. — Steve, Apr 16 '13 at 15:27
Look at your connection string and check if there is a substring `MultipleActiveResultSets=True;` — Steve, Apr 16 '13 at 15:28
Yeah it is - here is my connection string in settings: Data Source=###.###.###.###,4444;Initial Catalog=McD;User ID=Username;Password=Password;MultipleActiveResultSets=True;Connection Timeout=300; — Steve, Apr 16 '13 at 15:32
Thanks for trying - not sure why it would be timing out, as its such a simple query. — Steve, Apr 16 '13 at 15:40
Try to change the SqlCommand.CommandTimeout value just before executing the insert `conSQL2.CommandTimeout = 300` (5 minutes) — Steve, Apr 16 '13 at 15:43
Can you post what you did...........so everyone knows the fix. I'm curious myself! — granadaCoder, Apr 17 '13 at 18:18

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

I think you are blocking yourself.

Use one SqlDataReader to read all the data, and put it into a simple DTO object and collecton of DTO objects. Dispose/CLose of this SqlDataReader completely.

THEN try to send the emails, then issue a single UPDATE command.

Another way to say it, try to UNNEST comSQL2 from inside comSQL.

Here is a simple IDataReader to DTO example.

Why is DataTable faster than DataReader

edited May 23 '17 at 12:12

Community

1
1

answered Apr 16 '13 at 16:24

granadaCoder

26,328
10
113
146

You should always use IDataReader's as quickly as possible. You're "hanging out" on the email send within the IDataReader, which is NOT a good practice, IMHO. Get the IDataReader, use it quickly, put the data in a dto/dtoCollection, and then do something with the dto/dtoCollection. – granadaCoder Apr 16 '13 at 16:29
Thanks - I did think this might be the problem, I guess the data size may be something to do with why it is suddenly blocking. I will give it a go. Thanks, Steve. – Steve Apr 16 '13 at 18:29
Your Connection(true) may have some voodoo to it as well. That looks like somebody trying to be "clever". Take a look at this posting: http://stackoverflow.com/questions/328005/c-sharp-closing-sql-objects-best-practice – granadaCoder Apr 17 '13 at 13:33

score 0 · Answer 2 · answered Apr 17 '13 at 18:19

It appears that I had to un-nest the 2 sqldatareaders as mentioned above.

On the code below, depending on the result of the email process the Primary Key was added to a list:

                If EmailFile(madTO, madCC, madBCC, "", isHTML, Priority, Subject, Body) Then
                    intSent.Add(CInt(dr("Email_ID").ToString))
                Else
                    intFailed.Add(CInt(dr("Email_ID").ToString))
                End If

Then once the send process had completed I then run the update query on the 2 seperate lists or primary keys to update.

    Using comSQL2 As New SqlCommand("UPDATE Seq_Emails SET [Status] = @Status, [Date_Sent] = @Date WHERE Email_ID = @ID", conSQL)
        For Each Email_ID As Integer In intSent
            comSQL2.Parameters.Clear()
            comSQL2.Parameters.Add("@ID", SqlDbType.Int).Value = Email_ID
            comSQL2.Parameters.Add("@Status", SqlDbType.NVarChar).Value = "Sent"
            comSQL2.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now
            comSQL2.ExecuteNonQuery()
        Next
        For Each Email_ID As Integer In intFailed
            comSQL2.Parameters.Clear()
            comSQL2.Parameters.Add("@ID", SqlDbType.Int).Value = Email_ID
            comSQL2.Parameters.Add("@Status", SqlDbType.NVarChar).Value = "Failed"
            comSQL2.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now
            comSQL2.ExecuteNonQuery()
        Next
    End Using

I'm sure there are much easier ways to do this - however it did the trick for me in this case.

Ok. You're still "hanging out" with the Email_Send inside the DataReader. So keep in mind that if your smtp server slows up at any point, this whole routine could block other database requests. I still suggest refactoring to using the Idatareader as quickly as possible (push into DTO's), close the reader. Then start sending emails. Then after all the email attempts, go back and update the rows. You've come so far, and the DTO refactor would take an hour, and you'd wouldn't be blocking other db stuff when the smtp server slows down. — granadaCoder, Apr 17 '13 at 19:02
Hi - Yeah I see your point, I have other parts of this application which dump the sql results to a datatable first, however this seems to be working for now. I may change in the future. Cheers for your help. — Steve, Apr 17 '13 at 22:30

Simple Console Application SQL Update Query - Timing Out

2 Answers2