Curious Problem..

  • Thread starter Thread starter Rob Meade
  • Start date Start date
R

Rob Meade

Hi all,

I have written a small windows service which checks a directory for a new
file, upon receiving one writes a line to the log file and then copies the
file to SQL server.

I've had a few problems with it today, so have been running it in debug
mode - when I do so and step through its all perfect - no problems at all -
as soon as I remove the debugger.launch() line and run the service - whilst
it doesn't error, the database end seems to fail, ie, no new row is
written..

Can anyone suggest what might be wrong here? The service is using a
database connection which specifies the user id and password to use on the
database, so my initial though of "oh it must be permissions" may well be
wrong...

The service is running under the LocalSystem account.

If anyone has any ideas I'd love to hear from you...this has gone quite well
thus far but cant really run it in debug mode for ever! :o)

Thanks in advance for any help - will post code if applicable.

Regards

Rob
 
Rob, it may be a security problem, especially if the directory is a network
drive. I'm not sure, but attaching the debugger may run the service as the
interactive user. A quick test should be to run the service as your own
login, rather than the local system account, and see if it works then.
 
...
Rob, it may be a security problem, especially if the directory is a network
drive. I'm not sure, but attaching the debugger may run the service as the
interactive user. A quick test should be to run the service as your own
login, rather than the local system account, and see if it works then.
Click to expand...

Hi Chris,

I can confirm that it isnt a network drive - I have one server running both
Windows Server 2003 and SQL Server 2000 as a dev box.

I will try the login idea you suggested - *if* it does then work as it
should - what am I going to need to do to make it work? As I mentioned the
only part failing was the SQL update - which confused me considering I was
specifying the user/pwd to use for that in the code..

Any further info would be appreciated,

Regards

Rob
 
As I mentioned the only part failing was the SQL update - which confused
me considering
I was specifying the user/pwd to use for that in the code
Click to expand...

Rob, I saw this, and is why I thought you may be barking up the wrong tree.
Why are you so sure it is the SQL failing? What is your connection string?
 
...
Rob, I saw this, and is why I thought you may be barking up the wrong tree.
Why are you so sure it is the SQL failing? What is your connection string?
Click to expand...

Hi Chris,

Thanks again for the reply.

Well, when I say 'failing' maybe thats not the correct phrase as it clearly
is ok in debug mode - the reason I know its not 'firing' (for want of a
better word) is that no row is being written to the database..

I've cleared the table right down, I can run the service, test it a couple
of times, I get 2 rows in the event log, 2 rows in the database, then I
switch off the debug mode and run it again - 1 row in the event log - no
rows added to the database..

Connection string is:

strConnection = "user id=testservice;data source=""TITUS\KWS"";initial
catalog=testservice; password=testservice"

the entire database bit of my code looks like this:

' Declare variables
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim strConnection As String
Dim strSQL As String

' Define connection string
strConnection = "user id=*******;data source=""TITUS\KWS"";initial
catalog=testservice; password=*******"

' Create and open our database connection
objConnection = New SqlConnection(strConnection)
objConnection.Open()

' Generate SQL statement
strSQL = "exec storeEmail" & " '" & strStream & "' "

' Execute command
objCommand = New SqlCommand(strSQL, objConnection)
objCommand.ExecuteNonQuery()

' Close and clear our objects
objCommand.Dispose()
objCommand = Nothing
objConnection.Close()
objConnection = Nothing


Any thoughts?

Regards

Rob
 
...
A quick test should be to run the service as your own
login, rather than the local system account, and see if it works then.
Click to expand...

In visual studio upon clicking on the ProjectInstall.vb file I can then
click on the ServiceProcessInstaller1 and there is a property named
account - the only options are:

Local Service
Network Service
Local System
User

It was running with local system before, I have tried User this evening,
however you are unable to pre-populate this with any user details - instead
when I try to install it it then prompts for the details - upon doing so the
install failed..

?

Rob
 
...
A quick test should be to run the service as your own
login, rather than the local system account, and see if it works then.
Click to expand...

I see what you mean now sorry - my fault, you meant under the administrative
tools/ services change the user - I've done that change it to 'my' NT Login
and now it works fine....

Erm...what does this mean then?

Regards

Rob
 
I've done that change it to 'my' NT Login
and now it works fine....
Click to expand...

That would actually appear to be an exaggeration - it worked ONCE - I then
sent another email through and the event log updated, but the database
didn't...

I've tried restarting the service and still the same problem - the table in
SQL Server is not getting update?!

This isn't quite going to plan.. :o/

Rob
 
Rob, your connection string looks good. Add a test piece to your service to
insert a test record in a test table (run this code first before anything
else, so if something fails it does not effect the test code), to confirm
your database access works.
If this works, write a test piece to delete a dummy file from the directory,
insert the code right after the SQL server test piece, to confirm you have
access to the directory.

To run a service as a specific user (you don't use Visual Studio), open
control panel, go to services, find your service, right-click, then
properties, then the Log On tab, and change the user. Ensure that you
stop/start the service.
 
...
Rob, your connection string looks good.
Click to expand...

Thank you :)
Add a test piece to your service to insert a test record in a test table
Click to expand...
(run this code first before anything
else, so if something fails it does not effect the test code), to confirm
your database access works.
Click to expand...

okey dokey - I'll copy the database code and dump it at the top and just
write a single entry that means something here etc..
If this works, write a test piece to delete a dummy file from the directory,
insert the code right after the SQL server test piece, to confirm you have
access to the directory.
Click to expand...

I will but I'm 100% sure that I have access to the directory- because the
code returns the full path to the file that has just been created/deleted
and writes that to the event log - I'll do it anyway..

Will do this now and post back in a bit - thanks for the continued help :)

Rob
 
...here is the code from the service


Imports System.IO
Imports System.Data.SqlClient

Public Class ScanFolder
Inherits System.ServiceProcess.ServiceBase

Public folderToWatch As New FileSystemWatcher

#Region " Component Designer generated code "
Public Sub New()
MyBase.New()
' This call is required by the Component Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call
End Sub
'UserService overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
' The main entry point for the process
<MTAThread()> _
Shared Sub Main()
Dim ServicesToRun() As System.ServiceProcess.ServiceBase
' More than one NT Service may run within the same process. To add
' another service to this process, change the following line to
' create a second service object. For example,
'
' ServicesToRun = New System.ServiceProcess.ServiceBase () {New Service1,
New MySecondUserService}
'
ServicesToRun = New System.ServiceProcess.ServiceBase() {New ScanFolder}
System.ServiceProcess.ServiceBase.Run(ServicesToRun)
End Sub
'Required by the Component Designer
Private components As System.ComponentModel.IContainer
' NOTE: The following procedure is required by the Component Designer
' It can be modified using the Component Designer.
' Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
'
'Service1
'
Me.ServiceName = "ScanFolder"
End Sub
#End Region

Protected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.
' Debugger.Launch()
' Declare variables

Dim strSysDir As String
'strSysDir = System.Environment.SystemDirectory()
strSysDir =
"C:\Inetpub\mailroot\mailbox\kingswoodwebservices.co.uk\P3_Support.mbx"
folderToWatch.Path = strSysDir
With folderToWatch
.NotifyFilter = .NotifyFilter Or NotifyFilters.FileName
.NotifyFilter = .NotifyFilter Or NotifyFilters.Attributes
' To watch only emails
.Filter = "*.eml"
'To watch sub directories....
.IncludeSubdirectories = True
End With

AddHandler folderToWatch.Created, AddressOf newLog
'AddHandler folderToWatch.Changed, AddressOf newLog
'AddHandler folderToWatch.Renamed, AddressOf newLog
'AddHandler folderToWatch.Deleted, AddressOf newLog
folderToWatch.EnableRaisingEvents = True

End Sub

Private Sub newLog(ByVal Source As Object, ByVal evt As FileSystemEventArgs)

If evt.ChangeType = WatcherChangeTypes.Created Then
writeToLog(evt.FullPath, "Created")
'ElseIf evt.ChangeType = WatcherChangeTypes.Deleted Then
' writeToLog(evt.FullPath, "Deleted")
'ElseIf evt.ChangeType = WatcherChangeTypes.Changed Then
' writeToLog(evt.FullPath, "Changed")
End If

End Sub

Private Sub writeToLog(ByVal filePath As String, ByVal fileManip As String)

Dim evtLog As New EventLog
If Not evtLog.SourceExists("ScanFolder") Then
evtLog.CreateEventSource("ScanFolder", "ScanFolder")
End If

evtLog.Source = "ScanFolder"
evtLog.Log = "ScanFolder"
evtLog.WriteEntry("ScanFolder", "File " & filePath & " has " & fileManip
& " by " & Environment.UserName.ToString, EventLogEntryType.Information)

parseEmail(filePath)

End Sub

Private Sub parseEmail(ByVal path As String)

' Declare variables
Dim objStreamReader As StreamReader
Dim strStream As String

If File.Exists(path) Then
' Open file, populate variable, close file
objStreamReader = New StreamReader(path)
strStream = objStreamReader.ReadToEnd()
objStreamReader.Close()

' Declare variables
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim strConnection As String
Dim strSQL As String

' Define connection string
strConnection = "user id=*****;data source=""TITUS\KWS"";initial
catalog=testservice; password=*****"

' Create and open our database connection
objConnection = New SqlConnection(strConnection)
objConnection.Open()

' Generate SQL statement
strSQL = "exec storeEmail" & " '" & strStream & "' "

' Execute command
objCommand = New SqlCommand(strSQL, objConnection)
objCommand.ExecuteNonQuery()

' Close and clear our objects
objCommand.Dispose()
objCommand = Nothing
objConnection.Close()
objConnection = Nothing

End If

End Sub

Protected Overrides Sub OnStop()
' Add code here to perform any tear-down necessary to stop your service.
End Sub

End Class
 
I dont think it'll matter but I have installed the service using the
InstallUtil stuff from the build directory - ie,

C:\Documents and Settings\MeadeR\My Documents\Visual Studio
Projects\ScanFolder\bin\ScanFolder.exe

It installs fine but it just occured to me that as this isn't in any
'system' or 'system32' directory or anything whether that might have an
affect...?

Rob
 
...
Add a test piece to your service to
insert a test record in a test table (run this code first before anything
else, so if something fails it does not effect the test code), to confirm
your database access works.
Click to expand...

Hi Chris,

I added it in the onstart section of the service, sure enough it wrote a row
to the database - I've used the same table and same connection string to
ensure that the test is a good'un...

I then stopped the service and started it again - another row was written to
the database.

I have now just changed the user account stuff from log on locally (which it
defaulted back to after I rebuilt the service) to meader (me) and the test
database code worked still...

So both logon locally and meader AND the database connection are sound (in
my opinion).

Regards

Rob
 
...
If this works, write a test piece to delete a dummy file from the directory,
insert the code right after the SQL server test piece, to confirm you have
access to the directory.
Click to expand...

I added a text file in the mailbox directory named "delete-me.txt"

I then added the code to delete this file within the onstart section but
after the database test code.

Uninstalled/rebuilt/installed the service - started it - using the logon
locally account it successfully completed both test tasks; 1 row to
database, file deleted.

(soz for delay - had to look up some code for deleting files!)

Next? :o)

Regards

Rob
 
Hi Chris,

I have amended my code slightly - before the part (see "incase it helps") -
where I start declaring my database connection variables/objects etc...

I have changed the code to:

If File.Exists(path) Then

' Open file, populate variable, close file
objStreamReader = New StreamReader(path)
strStream = objStreamReader.ReadToEnd()
objStreamReader.Close()

ElseIf Not File.Exists(path) Then

strStream = "No file path was received"

End If

strStream is what should be written to the database - I figured if I did
this I could be guaranteed a value that 'should' get written to the
database - since doing this I still do not get a row written - I am
therefore wondering now whether this sub is actually getting called from the
sub above or not? (see my line parseEmail(filePath) )

Regards

Rob
 
Erm - what the hell!

Ok - get this - with the database code BEFORE the call to the parseEmail
sub - I get a test row written to the database as we would expect - BUT - I
also get the row that should have been written to the database containing
the email - have tested this 3 times thus far - using Mail2Web.com to send
the emails through (as development box is sending and receiving)...

Have just tested from dev box - and yes, its working as it should too.....

So - why does calling the database BEFORE calling the sub fix the problem
then - bit odd aint it??

Off for food now - will check back later - thanks for the help thus far :o)

Regards

Rob
 
Rob, sorry, I did not really look at your code, and I won't have time
either, but I can just give you some pointers.
The File Watcher runs on it's own thread, so what you do must be thread
safe. When the event occurs, open the connection as a stack variable
(declare it just before you use it), write, dispose the connection, etc.
Test one piece at a time, for example when a new email comes in, just beep,
don't do anything in the database/event log, etc, until you know that it
works, then add another piece and move the beep to the new piece, etc.
 
Back
Top