sql connection in windows service doesn't work!

[I appreciate your help.... anony]

Hi all,

Ive made a Windows Service that basically makes connections to a Sql
database, when it connects it runs a stored procedure. Below is the
static procedure. This procedure is in the Windows Service and the
procedure runs and an Eventlog is successfully written with no
exceptions. If I take this simple code and put it in a test console
app with the same connection, stored procedure and email address it
runs no problem. The problem I have is that I get no errors so im
thinking its some kind of Threading issue?

I just dont understand why this code with the same parameters can work
fine in a stand alone console app rather than incorporated into a
windows service. And still the windows service doesn't give me any
error, and the stored procedure doesn't run! I have verified that the
stored procedure is there and works!


static void runme(string connection, string remove, string
emailaddresss)
{
try
{
SqlConnection con = new SqlConnection(connection);
SqlCommand com = new SqlCommand(remove);
com.CommandType= CommandType.StoredProcedure;
SqlParameter myParm = new
SqlParameter("@email",SqlDbType.NVarChar,25);
myParm.Value = emailaddress;
com.Parameters.Add(myParm);
con.Open();
com.Connection = con;
com.ExecuteNonQuery();
con.Close();
com = null;
con = null;
EventLog.WriteEntry("MMS", " " + connection + " " +
remove + " " +emailaddress);
}
catch(System.Exception e)
{
EventLog.WriteEntry("Service", "Exception is as
follows: " ex.Message);

}
}


Thanks very much for your help.

phil

 

[Jan Bannnister]

Hi phil

How are you calling your static runme function? Is it one
of the Service Event handlers or in a Thread?
If you want it to be called periodicly you will need to
create a worker thread for it to be called from. (See ms-
help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frl
rfSystemThreadingThreadClassTopic.htm if you have VS 2003)

Hope that helps

 

[Miha Markic]

Hi there,

Under which account is your service running?
If you use trusted connection that account is used and probably isn't
allowed to connect to Sql server.
However, this does not explain why there is no eventlog entry.

 

[I appreciate your help.... anony]

Thanks very much for your reply

Well im using VS.Net 2002, I tried two things... First I just tried to
call it with an event timer ever n minutes, the static function gets
called as the eventviewer has a record of it. But it didn't call my
Sql stored procedure. Then I created a separate
System.Diagnostic.Process with the StartInfo settings with exactly the
same function but in a standalone app. Again the functions got called,
but the Sql was not called again! Its a little baffling for me. I have
looked at "window station" so I ticked my service to "interact with
desktop" but still no hope ;-(

As I dont have 2003 help file plz could you put a link on the msdn
reference plz?

thanks again

phil

 

[I appreciate your help.... anony]

hi,
My service was running under the default "Local System", I did
try changing it to a full admin, but no luck ;-( I must of not
communicated clearly, the strange thing is that there is an event log
entry, and no exception occured, which would leave me to believe that
the function was successful, but it obviously isn't!

I have tried everything I know, thanks very much for your help

phil

 

[I appreciate your help.... anony]

hi,
My service was running under the default "Local System", I did
try changing it to a full admin, but no luck ;-( I must of not
communicated clearly, the strange thing is that there is an event log
entry, and no exception occured, which would leave me to believe that
the function was successful, but it obviously isn't!

I have tried everything I know, thanks very much for your help

phil

 

[Tim Kohler]

You might try debugging your service and stepping through the code where you
are connecting to SQL server and calling the stored proc to see if you can
notice an error condition.

 

[Miha Markic]

In addition to Tim, You could put some trace stataments inside your service
that ouputs the executed lines, etc to an output file.

--
Miha Markic - DXSquad/RightHand .NET consulting & software development
miha at rthand com

Developer Express newsgroups are for peer-to-peer support.
For direct support from Developer Express, write to (e-mail address removed)
Bug reports should be directed to: (e-mail address removed)
Due to newsgroup guidelines, DX-Squad will not answer anonymous postings.

 

[I appreciate your help.... anony]

Hey Guys,

the thing is you cant step through a windows service, u can step
throught the "onstart" method. I didn't put trace messages, but that
is what EventLog.WriteEntry is showing that there is no errors
otherwise an exception would of been shown right? This is why I put
the EventLog.WriteEntry setting in the statement before the "catch"
statement and an EventLog.WriteEntry within the Exception statement
also.

phil