Unable To Execute Sql Command In Windows Service

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 200

Solution 1:

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 rowfromSQL
   while read was successful
     send an email
     read a rowfromSQL

   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.

Solution 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 ...

Solution 3:

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

Solution 4:

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