SQLce running as a service issue.

Hello all:

    I am writting a windows service to handle some transfer of data and one of the things I need to do is udate a SQLce Database.  I have several functions which I transfered from my original application (Before it was changed to a service) like CleanUpDB() which deletes records that are no longer needed.  This function works great.  No issues.  On the other hand if i try to update any data in the datqbase nothing happens.  Although i have cut and pasted the code from my "WORKING" app to my service app for some reason now the update isn't happening.
Am I missing something here?

Thanks
Dave

Private Sub CleanUpDB()
        'Deletes old records in the database.  see SQL Constant "CleanupPackets" for time frame. 
        Dim da As New SqlCeDataAdapter
        Dim con As New SqlCeConnection(ConString)
        Dim ra As Integer
        con.Open()
        Dim RemoveOldPackets As New SqlCeCommand(CleanupPackets, con)
        ra = RemoveOldPackets.ExecuteNonQuery()
        EventLog.WriteEntry("CleanUp removed: " & ra & " Record(s) from the Database.")
        con.Close()
    End Sub
 
 
    Private Sub InformDBPacketSent(ByVal RefNumber As String)
        EventLog.WriteEntry("InformeDB Started: " & RefNumber)
        Dim da As New SqlCeDataAdapter
        Dim con As New SqlCeConnection(ConString)
        con.Open()
        Dim ThisPacket As String = PacketSent + RefNumber + "'"
        EventLog.WriteEntry("Select command: " & ThisPacket)
        da.SelectCommand = New SqlCeCommand(ThisPacket, con)
        Dim ds As New DataSet
        da.Fill(ds, "Packets")
        Dim dt As DataTable = ds.Tables("Packets")
        Dim row As DataRow
        If dt.Rows.Count > 0 Then
            If dt.Rows.Count = 1 Then
                If Verbose Then EventLog.WriteEntry("DB Found 1 Packet for Ref#: " & RefNumber)
                row = dt.Rows(0)
                row("PacketSendSuccessful") = True
                row("PacketEndSendTime") = Now
                Dim ph As String = IIf(Not row.IsNull("PacketHistory"), row("PacketHistory", DataRowVersion.Current), "")
                If Verbose Then EventLog.WriteEntry("packet history before inform added text." & vbCrLf & ph)
                ph = ph & "Packet End Transmission " & Now & vbCrLf
                row("PacketHistory") = ph
            Else
                EventLog.WriteEntry("DB Found " & dt.Rows.Count & "Packet(s) for Ref#: " & RefNumber)
                Dim counter As Integer = 0
                For counter = 0 To dt.Rows.Count - 1
                    row = dt.Rows(counter)
                    row("PacketSendSuccessful") = True
                    row("PacketEndSendTime") = Now
                    Dim ph As String = IIf(Not row.IsNull("PacketHistory"), row("PacketHistory", DataRowVersion.Current), "")
                    ph = ph & "Packet End Transmission " & Now & vbCrLf
                    row("PacketHistory") = ph
                Next
            End If
            Dim cb As New SqlCeCommandBuilder(da)
            EventLog.WriteEntry("Before update copmmand")
            da.Update(ds, "Packets")
            con.Close()
            EventLog.WriteEntry("After connection close")
        Else
            EventLog.WriteEntry("Packet not found: " & RefNumber)
            con.Close()
        End If
        EventLog.WriteEntry("DB informed of send: " & RefNumber)
    End Sub

Open in new window

MedSouthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dublingillsCommented:
Hi,

Is the Windows Service running in the same security context as your user account?

0
MedSouthAuthor Commented:
No I have it running in local system.
0
dublingillsCommented:
If you start the service under your own user account are you able to update the database?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MedSouthAuthor Commented:
No I can't.   Durring my testing I'm manually starting it while logged in to my user profile.  I can Delete records but the update doesn't work.  I have not tried to add yet. but if I have to I guess i could delete and add it back...
0
MedSouthAuthor Commented:
Oh I see what you are saying...   change the service to run under  my account information and see if it updates...  I will try it.
0
dublingillsCommented:
Forgive me but my understanding is that manually starting a service when logged into your user profile doesn't start the service under your user account, the service starts as whatever account is specified in the Log On tab of the service properties.
0
dublingillsCommented:
Also, at what point does the code fail?  Which messages are written to the event log?
0
MedSouthAuthor Commented:
I set it to run as a user and provided it with a account with administrator rights still failed.  As a side note you can set a service to start up in "LocalSystem" it doesn't require a user account to run.

anyway the event logs get to "Before update copmmand" line #49 of the above code.

 
0
dublingillsCommented:
The Local System account is actually the NT AUTHORITY\SYSTEM account which is a hidden account because it runs highly privileged.

Anyway, back to the update issue - can you add change the following (the idea is to check the update command being created by the command builder):
Dim cb As New SqlCeCommandBuilder(da)
EventLog.WriteEntry("Before update copmmand")
da.Update(ds, "Packets")
 
 
TO
 
 
Dim cb As New SqlCeCommandBuilder(da)
EventLog.WriteEntry("Before update copmmand")
cb.GetUpdateCommand()
EventLog.WriteEntry(cb.GetUpdateCommand().CommandText)
da.Update(ds, "Packets")

Open in new window

0
MedSouthAuthor Commented:
I did as you asked and still get to the same event before it locks
I don;t get the event entry for the command.text
0
dublingillsCommented:
OK I need to have a bit more of a think as that seems really weird. Am off to UK tomorrow so will have a think over the wknd and get back to you monday, hopefully someone else may have something for you before then.
0
MedSouthAuthor Commented:
OK I figured it out.    It was a couple of things actually.

1st thing was Line 29 from my original post code snippit
row = dt.rows(0)    changed it to      
row = dt.rows.item(0)

2nd thing was my select statements.   I know it is good practice to select only the fields you want to work with so that is what I was doing.  

ie:  SELECT FirstName, LastName, DateOfBirth FROM People WHERE FirstName = "John"
this would fail every time with the commandbuilder update

if I changed it to SELECT * FROM People WHERE FirstName = 'John'
and did my commandbuilder update everything worked perfect.

I do not understand the difference but Hay it works now...   Thanks for you time and effort dublingills
Have a great time in the UK.

thanks again.
Dave
0
dublingillsCommented:
Glad you got it sorted.

Regards,
Richard
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MedSouthAuthor Commented:
THanks again dublingills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.