Unable to execute SQL command in windows service

Question

I'm working on creating a windows service that will send emails to a customer when they are within a month of having their submission expire. I'm using vb.net in Visual Studios 2008

Because it's a windows service it's very difficult to debug. Trying to narrow down my error I created a "sendDebugEmail" method that sends me an email if it gets to a certain line. The emails never make it past "dr = cmd.ExecuteReader()"

I'm wondering what I am doing wrong. My SQL statement should work fine. I've tested it in my SQL server database.

I've created a dummy_database that I just made in sql server as well. I added an INSERT sql statement for the dummy table i have in there just to see if i could actually access a database. All the table takes in is the line number and time it was sent. When I run my windows service that database updates just fine.

Any help would be appreciated. Thanks

Dim conn As New SqlConnection(connString2)

    sendDebugEmail("134")

    SQL = "Select email FROM _Customer WHERE custID in (SELECT custID FROM _OnlineCustomer WHERE ExpirationDate <= '6-20-12' AND ExpirationDate >= '6-10-12')"
    Dim cmd As New SqlCommand(SQL, conn)


    sSubject = "hello"
    sBody = "This is test data"
    Dim dr As SqlDataReader

    sendDebugEmail("143")

    Try
        dr = cmd.ExecuteReader()   // This is were it stops

        sendDebugEmail("147")

        While dr.Read

            sendDebugEmail("152")

            Try
                LogInfo("Service woke up")
                Dim i As Integer = 0
                ' Prepare e-mail fields
                sFrom = "test@gmail.com"
                sTo = "test1@gmail.com"
                sCc = "test2@gmail.com"
                Dim oMailMsg As MailMessage = New MailMessage
                oMailMsg.From = sFrom
                oMailMsg.To = sTo
                oMailMsg.Cc = sCc
                ' Call a stored procedure to process the current item
                ' The success message
                oMailMsg.Subject = sSubject + "(Success)"
                oMailMsg.Body = sBody + "Email has been sent successfully."
                ' Send the message
                If Not (oMailMsg.To = String.Empty) Then
                    SmtpMail.Send(oMailMsg)
                End If

            Catch obug As Exception
                LogEvent(obug.Message)
            Finally

            End Try
        End While
    Catch ex As Exception

    Finally
        dr.Close()
        cmd.Dispose()
        conn.Close()
        conn.Dispose()
    End Try
End Sub

/////////////////////////////////////////////////////////////////////////////////

Problem Solved: I set up my connection but I never opened it.
I needed conn.open()

The thing that helped me most was adding this code into my last catch statement:

sendDebugEmail(ex.Message & vbcrlf & ex.stackTrace)

It send me an email of the stackTrace and made it very easy to debug

Answer 1

Problem Solved: I set up my connection but I never opened it.
I needed conn.open()

The thing that helped me most was adding this code into my last catch statement:

sendDebugEmail(ex.Message & vbcrlf & ex.stackTrace)

It send me an email of the stackTrace and made it very easy to debug

Answer 2

If you are sure about your code (with no exceptions) i think you should check the authentication you are using to connect the SQL server(inside the connection string within the app.config file/inline code of the windows service).

If you are using SQL authentication for this (check the connection string for user name sa and its password) setting the account type of the windows service to LocalService will help you.

If the SQL connection is using windows authentication then setting the account type of the windows service to LocalSystem will help you.

The Account type modification can be done after installation also. For this go to Control panel->Administrative tools->Services->YourService right click and select Propertes->Logon and perform it there. If you are selecting the LocalSystem (windows authentication) you will be asked to enter the login credentials of the account in which the service is running.

In the case of windows authentication in SQL connection the credentials of the account in which the service is running will be taken for SQL connectivity also.

Hope this helps ...

Answer 3

One more suggestion put a sleep statement on your process when it starts so oyu have time to attach to it

Answer 4

Are you trapping and swallowing exceptions? If you are, stop. Let exceptions service crash the service: the exception will be logged in the Event log. The only exceptions you should trap are those you can actually recover from (though its valid to catch the exception, log it and rethrow it via throw;).

Have you instrumented your code with log4net (http://logging.apache.org/log4net/), or something similar? You should be, especially for a daemon like a Windows service — how else are you (or operations) going to diagnose problems with the service when the occur (as they will).

Edited to note:

1. You should be using using statements: all those ADO.Net objects are IDisposable. It makes for cleaner code.

2. Consider using a SqlDataAdapter to fill a DataTable or DataSet with your results set. The pattern you're using:

   read a row from SQL
   while read was successful
     send an email
     read a row from SQL
   

   will ultimately lead to blocking in your database. Talking to a mail server has the potential for a high latency. If the mail server doesn't answer, or you have network congestion, or any of a number of other reasons, you're going to be left hanging until the mail is sent or an exception is thrown due to timeout. And your SQL Query is going to be sitting there with read locks on the table and indices from which you're reading data, blocking people attempting to do updates, inserts or deletes. Your production DBAs will be...vexed. You want to keep your locks moving and get the locks released as quick as you can.