Can Window Service Call a Stored Procedure?

I used the same lines of codes to call a SQL server store procedure to export sql data to an XML file:
1. If I call the stored procedure from a window form application, it works and generates the file at the designated location.
2. If I call the stored procedure from a window service application with a setup project, and the install process run well, and I started the service, and checked the event log, it showed the service start successfully, BUT it FAILED to generate the file at the destination folder. I also run everything as administrator, but it still failed to generate the file. It made me wonder if a window service can call a stored procedure.
Please help.
Because, again, mine ran fine with the window form appl, but window service, I would like to have your sample codes. I have googled around for 3 days now, but could not find the answer. I use vs 2008, sql server 2008, and Windows 7

Thanks!

where are you trying to write the file to?

-tg

Welcome to the forum.

Yes - you can call a sproc from a window service.

Have you any error logging in the service? Create a text file and log "start of app" and the top and then "log action" points within the logic.

Also - proper TRY/CATCH error trapping with output to the error log.

I use NorthWind database to test.

Here are samples of my codes:

Protected Overrides Sub OnStart(ByVal args() As String)
EventLog1.WriteEntry("In OnStart")
Dim strSQL As String = "exec [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]"
Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
cnn.Open()

Dim cmd As New SqlCommand(strSQL, cnn)
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()
cnn.Dispose()
End Sub

Here is the stored prod:

CREATE PROCEDURE [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]

AS
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\temp\WinSrvc_SP_BCP_OUT_XML_'+CONVERT(char(8),GETDATE(),1)+'.xml','/','-')

SET @bcpCommand = 'bcp "SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories [Categories] FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

SET @bcpCommand = @bcpCommand + @FileName + '" -SMAIN\SQL2008 -T -c -r -t'
--PRINT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand, No_output

GO

put [code] xxx [/code] brackets around your code...

Try this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    Dim strSQL As String = "exec [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]"
    Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
    Try
          cnn.Open()

          Dim cmd As New SqlCommand(strSQL, cnn)
          cmd.ExecuteNonQuery()
          cmd.Dispose()
          cnn.Close()
          cnn.Dispose()
      Catch
          EventLog1.WriteEntry(ex.message)
      End Try
End Sub

---

What does your event log look like after you run this??

I applied the try and catch you recommended.

The event log shows: "In OnStart", exactly what I coded in the sub: EventLog1.WriteEntry("In OnStart")

So, It appears no error, but why it did not generate the file?

Please help.

Thanks,

Ok - create a table called DebugSproc_T

Code:

---

Create Table DebugSproc_T
    (DId int identity not null
    ,DMessage varchar(1000)
    ,TDate datetime
    ,Constraint PKDebugSproc
        Primary Key (DId)
    )

---

Now alter your sproc to be

Code:

---

CREATE PROCEDURE [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]

AS
Insert into DebugSproc_T values ('In Here',GetDate())
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\temp\WinSrvc_SP_BCP_OUT_XML_'+CONVERT(char(8),GETDATE(),1)+'.xml','/','-')

Insert into DebugSproc_T values (@FileName,GetDate())

SET @bcpCommand = 'bcp "SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories [Categories] FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

SET @bcpCommand = @bcpCommand + @FileName + '" -SMAIN\SQL2008 -T -c -r -t'
--PRINT @bcpCommand


Insert into DebugSproc_T values (@bcpcommand,GetDate())

EXEC master..xp_cmdshell @bcpCommand, No_output

Insert into DebugSproc_T values ('About to leave',GetDate())

GO

---

You can also put CATCH/BEGIN's in the SPROC to detect errors and put them to DebugSproc_T

I created the table, alter the sp, then start the service again, then run this command in SQL management studio:

select * from dbo.DebugSproc_T

but do not see any result (no row return) which I guess the service did not call the sp at all, and did not provide any error message!!!

Again, if I run the same codes in window form appl. it successfully generates the file.

Please help and greatly appreciate your patience!

sql Code:

DECLARE @FileName varchar(50)

I dont know why I get the feeling 50 characters arent enough for a full file path. It may not be related to your problem but i think you should make this at least 256.

This is most likely a permissions issue.

Each service runs with a specific account. I guess the Integrated Security means that the service will try to connect to the database using the credentials the service is running under.

Further, services cannot write to any location they like: again, a permissions issue. The service will only have access to the folders it is allowed access to.

I don't know if these are relevant to your case, but services are locked down to a greater extent if they run under the default credentials. That is, the credentials are different from an administrator account.

Did you enable the service to run under a specific account to eliminate that as an issue?

Quote:

---

Originally Posted by 9pepper

...Again, if I run the same codes in window form appl. it successfully generates the file.

---

That means that after you run that same code from a windows form appl. you do see rows in the DebugSproc_T table - right??

Change your code to this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    Dim strSQL As String = "Insert into DebugSproc_T values ('Insert',GetDate())"

---

When you run that simple insert - as a service - do you get a row?

If not then it most certainly is a permissions issue - which is easy to get around with sprocs by just granting EXECUTE permissions to a SQL role that is associated with the windows group the service runs under.

Code:

---

GRANT EXECUTE ON frmBuilding_Update TO StufilesUser

---

This grants execute permission on a sproc the StufilesUser - as SQL role that is associated with various windows groups.

Of course it could be that you aren't even able to connect to SQL at all - that the initial authentication failed.

Does your SQL service allow mixed mode authentication - both SSPI and SQL username/passwords??

First to answer your question regarding running the codes from my window form appl. Yes, it ran successfully and it did insert into the table as we coded in the sp.

Now I change the codes to your simple insert, it did not insert.

Before we conclude that is permission issue, I would like you to know that:

I ran everything as administrator.
I even turn off UAC (user access control) on my computer.
I run these codes at my home computer, it means everything in the same computer, vs2008, sql server 2008, and windows 7.

before this window service application, I also developed a SSIS package to export the same file to the same location, and I schedule the package, and it successfully generated the file.
I then created another windows service appl using filewatcher to copy the file created by the ssis package to another location, and this window service also worked fine.

I design this window service, with the thought to make it simple, not using both ssis package and the window service with filewatcher above. But somehow it does not work.

Please help and greatly appreciate your patience.

Forget absolutely everything you just said and everything about SSIS and whatever...

You have just proven that you cannot do a SIMPLE INSERT into a TABLE from the windows service.

That means your connection is somehow not working - right?

Why you aren't getting an error that is caught in the TRY/CATCH is beyond me.

Change it to this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    Dim strSQL As String = "Insert into DebugSproc_T values ('Insert',GetDate())"
    Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
    Try
          cnn.Open()
          EventLog1.WriteEntry(cnn.State)

          Dim cmd As New SqlCommand(strSQL, cnn)
          cmd.ExecuteNonQuery()
          EventLog1.WriteEntry('after exec')
          cmd.Dispose()
          cnn.Close()
          cnn.Dispose()
      Catch
          EventLog1.WriteEntry(ex.message)
      End Try
    EventLog1.WriteEntry("Leaving OnStart")
End Sub

---

After I modified the code, install, start the service, there is only one line in the event log and it show: "In OnStart"

I do not see: cnn.state, "after exec", and "leaving OnStart".

I guess it did not process the codes below the first line.

Where should I run the Grant permission command. I ran it in my northwind database, and it showed: Cannot find the object 'frmBuilding_Update', because it does not exist or you do not have permission.

Ok - change it to this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    Try
          EventLog1.WriteEntry("1")
          Dim strSQL As String = "Insert into DebugSproc_T values ('Insert',GetDate())"
          EventLog1.WriteEntry("2")
          Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
          EventLog1.WriteEntry("3")
          cnn.Open()
          EventLog1.WriteEntry(cnn.State)

          Dim cmd As New SqlCommand(strSQL, cnn)
          cmd.ExecuteNonQuery()
          EventLog1.WriteEntry('after exec')
          cmd.Dispose()
          cnn.Close()
          cnn.Dispose()
      Catch
          EventLog1.WriteEntry(ex.message)
      End Try
    EventLog1.WriteEntry("Leaving OnStart")
End Sub

---

Just out of my curiosity, may be Onstart is not the right place to put my codes there?

Not sure about that - but change it to what I just posted and see if you get more info in the log - I'm starting to think you don't have framework installed...

It still has only one line in the event log, and shows "In OnStart"

Can you provide me with sample codes? or if you have northwind, I can resend you all the code to test, it might be faster this way, before we move on to different direction.

Quote:

---

Originally Posted by szlamany

Ok - change it to this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    Try
          EventLog1.WriteEntry("1")
          Dim strSQL As String = "Insert into DebugSproc_T values ('Insert',GetDate())"
          EventLog1.WriteEntry("2")
          Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
          EventLog1.WriteEntry("3")
          cnn.Open()
          EventLog1.WriteEntry(cnn.State)

          Dim cmd As New SqlCommand(strSQL, cnn)
          cmd.ExecuteNonQuery()
          EventLog1.WriteEntry('after exec')
          cmd.Dispose()
          cnn.Close()
          cnn.Dispose()
      Catch
          EventLog1.WriteEntry(ex.message)
      End Try
    EventLog1.WriteEntry("Leaving OnStart")
End Sub

---

---

Are you saying this code only put one line in the ERROR LOG????

I guess you meant the event log when you said ERR log. Yes it showed only one line in the event log. (control panel - administrative tools - event viewer)

I didn't know where you were sending your log info - I had suggested you create a text file way at the beginning of this - I guess you are sending things to the EVENT log??

Try this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    EventLog1.WriteEntry("Still In OnStart")
End Sub

---

Do both messages get written to whereever you are sending them.

If not then you just wasted a bunch of your own time with an inadequate logging method that was never going to show whatever error was happening in the CATCH!

after so many attempt to build myself a window service application which did not work, I use the below walkthrough link to build this window service, and modified the OnStrat method as we discuss and revise so far:

http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx

and the stored proc in previous post.

If you have NorthWind you can try.

Please help and greatly appreciate your patience.

Quote:

---

Originally Posted by szlamany

I didn't know where you were sending your log info - I had suggested you create a text file way at the beginning of this - I guess you are sending things to the EVENT log??

Try this

Code:

---

Protected Overrides Sub OnStart(ByVal args() As String)
    EventLog1.WriteEntry("In OnStart")
    EventLog1.WriteEntry("Still In OnStart")
End Sub

---

Do both messages get written to whereever you are sending them.

If not then you just wasted a bunch of your own time with an inadequate logging method that was never going to show whatever error was happening in the CATCH!

---

Did you do this yet?

And no - I am only willing to help as I am doing here...

It still showed only one line "In OnStart".

I also tried to debug (start the service, then attach the process, but all the break point I set get hollow). Please help, I do not know how to see the error.

what should I do to see the error in the catch block?

Thanks!

Then remove ALL THE event log writing and instead put JUST ONE event log in the CATCH - seems you can get one error message written to the log mine as well make it a meaningful one.

My suggestion from Post #3 was to create a TEXT file and write to that - so you could put multiple messages - but try the single EVENT LOG writeline in your CATCH statement first.

here is the modified codes:

Protected Overrides Sub OnStart(ByVal args() As String)
File.WriteAllText("c:\temp\startOnStart1.txt", "Start")
EventLog1.WriteEntry("In OnStart")
Dim strSQL As String = "exec [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]"
Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
Try
File.WriteAllText("c:\temp\startOnStart2.txt", "Try")
cnn.Open()

Dim cmd As New SqlCommand(strSQL, cnn)
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()
cnn.Dispose()
Catch ex As Exception
File.WriteAllText("c:\temp\startOnStart3.txt", ex.Message)
EventLog1.WriteEntry(ex.Message)
End Try

File.WriteAllText("c:\temp\startOnStart3.txt", "End of Start")
EventLog1.WriteEntry("Still In OnStart")
End Sub

I do still get the "In OnStart" in the event log, BUT I do not get the outcome of this: File.WriteAllText("c:\temp\startOnStart1.txt", "Start") which is at the very top of my codes.

Please help.

if I add File.WriteAllText("c:\temp\startOnStart1.txt", "Start") to my window form appl, it does generate this .txt file. I think something has to do with window service appl.

Quote:

---

Originally Posted by szlamany

Then remove ALL THE event log writing and instead put JUST ONE event log in the CATCH - seems you can get one error message written to the log mine as well make it a meaningful one.

My suggestion from Post #3 was to create a TEXT file and write to that - so you could put multiple messages - but try the single EVENT LOG writeline in your CATCH statement first.

---

I said - first - put your ONLY SINGLE ONE Eventlog1.WriteEntry in the CATCH!!

By starting to use a TEXT FILE you have changed the entire debug environment.

The whole point of debugging is to narrow down issues and aspects - not open a new can of worms!

You obviously are having an access/permission issue.

The only thing you have working for you is a SINGLE WRITE to the EVENT LOG.

Let's see if we can make that WRITE be a meaningful message.

But regardless - I'm out of here for the night - 14 hour day!

Yes, now I leave ONLY SINGLE ONE Eventlog1.WriteEntry in the CATCH, it still shows "In OnStart".

Quote:

---

Originally Posted by 9pepper

Yes, now I leave ONLY SINGLE ONE Eventlog1.WriteEntry in the CATCH, it still shows "In OnStart".

---

Why would you leave the "In OnStart" constant in the WriteEntry???

Quote:

---

Originally Posted by szlamany

Ok - change it to this

Code:

---

.
.
.
      Catch
          EventLog1.WriteEntry(ex.message)
      End Try
End Sub

---

---

I changed to what you recommended, and it still shows "In OnStart".

Go back to my earlier post about the File.WriteAllText("c:\temp\startOnStart1.txt", "Start"), and I said it did not run this line which is on the very top of the OnStart method. I just wondering if the service actually process anything inside this OnStart method.

right now I do not know how it write to the event log "In OnStart"!?

I will be back in an hour.

Quote:

---

Originally Posted by 9pepper

I changed to what you recommended, and it still shows "In OnStart".

Go back to my earlier post about the File.WriteAllText("c:\temp\startOnStart1.txt", "Start"), and I said it did not run this line which is on the very top of the OnStart method. I just wondering if the service actually process anything inside this OnStart method.

right now I do not know how it write to the event log "In OnStart"!?

---

Since IN ONSTART is no longer present anywhere in your code that makes no sense...

question, when you start the service, does it stay running? I've seen cases where something goes wrong in the OnStart causing the service to unload.

Usually OnStart event of a service is used for setting things up for the service... not for doing the work. I see you're using integrated security for the db connection... unless the service account that the service is running under has been granted access (which would inherently grant ALL SERVICES access) then that's going to fail. I suggest using sql authentication login for this...

-tg

tg,

For now just forget about my codes that interact with sql server. please look at the post that I put this line of code on the very beginning of the onstart method: File.WriteAllText("c:\temp\startOnStart1.txt", "Start")

That is very simple write, but the service not even process that line.

Also to answer your question, yes it still running until I stop the service. I know it because right after I stop the service, I check the log, and see it write to the log "in OnStop" and the timestamp is right to the moment I stop the service.

Please help.

as I said earlier, I follow the instruction at this link to build my windows service appl.
http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx

then I modified the OnStart method to include the call the stored procedure as we discuss so far.

You can find the codes of the stored procedure in earlier post.

If you have NorthWind you can try. I believe at this point, you might want to try yourself, the fast way to find out the error.

Please help and greatly appreciate your patience.

I believe it does not take you more than 10 minutes to create the application and the store procedure to test.

Try this... go into Control Panel -> Administrative Tools -> Services.... find your service. Stop it, right-click, and select properties... got to the Logon tab... set it to run as a local service account (first radio button) and then make sure that the "Interact with desktop" is checked... save, re-start your service... see if it will write to the file then.

-tg

I tried your recommendation, but I did not work. Just to be clear, you said "local service account", but I see the first radio button is "local system account" and I select this button and also check the checkbox for "Interact with desktop". but it did not work.

Thanks, please help and patience with me.

This note is to the person responsible for this website:

I join this forum today, but you system shows I join on 2/12. you might want to correct the date.

that's Feb 12... as in Feb 2012.... not 12 Feb 2012...It doesn't track the actual day... only the Month/Year.

-tg

I still need your help. Please do not give up!

How would you suggest we help you?

This started as a huge red herring - thinking we had access to SQL permission issues.

Ended up that your service simply won't get started.

TG already gave you info on OnStart possibly being a bad place to have code like this.

You are up against creating a simple HELLO WORLD service and seeing if you can get it to run.

If a complicated example that you downloaded isn't working then why would you want to continue with that example - they obviously only ever got their example working on a development machine (or had a completely different setup for UAC and what not).

At any rate - I can't think of anything I can do to assist.

You might want to start a whole new thread with a title:

"How to get a simple Window Service to run on XYZ" where XYZ is the O/S you are trying to get the service running under.

Forum folk right now think this is a SPROC question - with nearly 50 posts and already at 2 pages. If I wasn't a SPROC expert I never would have got involved in this thread in the first place.

Start a new thread!! Ask a simpler question. Get a "Hello World" sample app running - then add your complexity.

I'm curious why you aren't already thinking like this? Is this a school assignment or are you working on this for your employer??

As I mentioned in earlier posts, I also have a ssis package scheduled to generate the file, then a different windows service with filewatcher which watch the designated folder for the new file created by ssis, when the new file created, this service copies the file to another folder.

I design this test windows service to combine the two, ssis & the service above to one service.

as you suggest, I did have a service working which is the filewatcher above. But the new service does not work.

If you think OnStart is not the place, could you tell where is the right place. I have googled for 3 days now, everybody talk and discuss, but no sample codes available, I would like to see a sample that call a sp successfully, to build my detail based on the sample, just forget the details inside my stored procedure. I need a sample just as a framework to develop my details.

As I mentioned in my original post, I need a sample codes, then I can figure out from there.

Thanks.

btw - I didn't see your email - I don't reply to emails anyway. They go to a bogus gmail account I have and filter the "vb subscription" emails to get forwarded to my real email account.

Quote:

---

Originally Posted by 9pepper

As I mentioned in earlier posts, I also have a ssis package scheduled to generate the file, then a different windows service with filewatcher which watch the designated folder for the new file created by ssis, when the new file created, this service copies the file to another folder.

I design this test windows service to combine the two, ssis & the service above to one service.

as you suggest, I did have a service working which is the filewatcher above. But the new service does not work.

If you think OnStart is not the place, could you tell where is the right place. I have googled for 3 days now, everybody talk and discuss, but no sample codes available, I would like to see a sample that call a sp successfully, to build my detail based on the sample, just forget the details inside my stored procedure. I need a sample just as a framework to develop my details.

As I mentioned in my original post, I need a sample codes, then I can figure out from there.

Thanks.

---

Did you have your other services running in the OnStart event?

I really think this thread is dead - it's two pages of meaningless run around that got you no where.

If you want me to "learn" you how to debug something like this - here's some ideas.

Remove everything from the OnStart event - make it look like the OnStart event of one of your other "successful" window services.

Does it run now?

Add one line at a time back in to the OnStart event.

When does it fail?

This is elementary debugging - right?

Another idea - take your successfully running service and start adding this new code to that service.

When does it break.

Here is what I rebuild my windows service appl.

Protected Overrides Sub OnStart(ByVal args() As String)
File.WriteAllText("c:\temp\startOnStart1.txt", "Starting")

Dim strSQL As String = "exec [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]"
File.WriteAllText("c:\temp\startOnStart2.txt", "Dim strSQL As String")

Dim cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
File.WriteAllText("c:\temp\startOnStart3.txt", "Dim cnn As New SqlConnection")

cnn.Open()
File.WriteAllText("c:\temp\startOnStart4.txt", "cnn.Open()")

Dim cmd As New SqlCommand(strSQL, cnn)
File.WriteAllText("c:\temp\startOnStart5.txt", "Dim cmd As New SqlCommand")

cmd.ExecuteNonQuery()
File.WriteAllText("c:\temp\startOnStart6.txt", "cmd.ExecuteNonQuery()")

cmd.Dispose()
File.WriteAllText("c:\temp\startOnStart7.txt", "cmd.Dispose()")

cnn.Close()
File.WriteAllText("c:\temp\startOnStart8.txt", "cnn.Close()")

cnn.Dispose()
File.WriteAllText("c:\temp\startOnStart9.txt", "cnn.Dispose() and END OF OnStart")
End Sub

It stops right after generates startOnStart3.txt. By the way I found at another forum: http://www.eggheadcafe.com/community...s-service.aspx

the replier to the same problem said: "The problem is that the OnStart method of a windows service has only one purpose: To start the service and return the result to the Windows ServiceManager. It allows 30 seconds only to do this. What you need to do is, in OnStart, you must immediately create a background thread that spins up an instance of your business class, initializes your timer, and does the work. Once again, you CANNOT do business logic in the OnStart method of a service and expect it to work."

But I am not sure what he meant "create a background thread that spins up an instance of your business class". I just go ahead add a .vb class to my project, and create the class as below, I modify the sub New so that it connect to sql and call the stored procedure right at the declaration of an instance of this class:

Public Class CallStoredProc
Private strSQL As String = "exec [dbo].[My_XP_CMDSHELL_BCP_SELECT_FOR_XML]"
Private cnn As New SqlConnection("Integrated Security=SSPI;Database=Northwind;Server=MAIN\SQL2008")
Public Sub New()
cnn.Open()
File.WriteAllText("c:\temp\startOnStart4.txt", "cnn.Open()")

Dim cmd As New SqlCommand(strSQL, cnn)
File.WriteAllText("c:\temp\startOnStart5.txt", "Dim cmd As New SqlCommand")

cmd.ExecuteNonQuery()
File.WriteAllText("c:\temp\startOnStart6.txt", "cmd.ExecuteNonQuery()")

cmd.Dispose()
File.WriteAllText("c:\temp\startOnStart7.txt", "cmd.Dispose()")

cnn.Close()
File.WriteAllText("c:\temp\startOnStart8.txt", "cnn.Close()")

cnn.Dispose()
End Sub
End Class

I then declare an instance of this class in the OnPause method as below:

Protected Overrides Sub OnPause()
Dim CallMyStoredProc As New CallStoredProc
End Sub

The reason I declare in the OnPause to make sure the service start at normal, then I pause the service to see how the windows system react. It showed: Window could not pause the service. The service did not return an error. If the problem persists, contact your system administrator.

I believe they all, onstart, onpause, onstop, and so on.. are the same, they do not allow enough time to do major processes inside the method.

Again, back to my question in previous post. Where is the right place in the windows service application to put your codes to call a stored procedure or any major processes? I have searched and googled, but could not find the answer to this very question.

Please help.

Great - you have solved your problem here. OnStart has a 30 second limit - a small window to work in.

Before you go down the path of threading - why are you making this a service and not simply a console app???? Services are expected to stay running waiting for additional "commands" to be sent and then start new threads to process them. Does you app just start - run a process - and exit?? If so I'm unclear on your need for a windows service. Who starts this service? When does it run?

I've only just started using multi-threading myself.

Look in post #1 of this thread - third code snippet. I'm looping through a file collecition and start a new thread on each file.

http://www.vbforums.com/showthread.php?t=673046

Then look at this thread - discussion on how to deal with the "threads" touching module level variables.

http://www.vbforums.com/showthread.php?t=673121

Thank you for your clarification and advice.

when I started this project, I thought it must be very simple to call a stored procedure from windows service as people say so everywhere on internet (unfortunately, people do not provide sample codes of successful calls or I have not found one yet). Mutithreading is a great alternative option, but for now I believe there should be a right event in a windows service application to call a stored procedure, and I will continue to search for the answer.

In the meantime, I will use my new consol application which I developed last night to work along with window task scheduler to solve my problem.
Thanks!

There IS a way too call a stored procedure from a windows service... no one has said you couldn't... you just SHOULD NOT do it in the OnStart event... that is for just INITIALIZING the service.... it's going to run once and that's it. That's why I said that this was not a good place for this kind of code.

What you're onStart code should do is to create a background thread, set some parameters, pass them off to the thread and let it do it's thing... that's it, nothing more. You only have 30 seconds to get the service up and running... so the less you do in the OnStart, the better. If it needs to do any heavy lifting, or even any kind of lifting, it goes in a thread.

-tg

@tg - I don't think a service is required for this - if I understand the OP it's a single task - one process - type of job. It doesn't have a "life" after that single cycle.

Quote:

---

Before you go down the path of threading - why are you making this a service and not simply a console app???? Services are expected to stay running waiting for additional "commands" to be sent and then start new threads to process them. Does you app just start - run a process - and exit?? If so I'm unclear on your need for a windows service. Who starts this service? When does it run?

---

The OP never answered these questions - and I believe they are key to determining if a service is needed - right?

I agree that a service might not be warranted... but he also did mention using a fileWatcher... and that the SSIS creates the file... which... you know what? I'm beginning to think the whole setup is wrong... if he's got an SSIS that creates the file... what's the problem? What is it that he's trying to do that couldn't be done in the SSIS package. It's pretty powerful... it does quite a bit... I built one that took a zip file, unzipped the contents, put the files into a holding folder, sucked them in (they were DB2 files being imported into SQL Server), ran some data validation, some clean up, then runs analysis on it and dumps it into the final tables...

So I'm now wondering what it is exactly that the OP is trying to do.

-tg

btw - I just created my first WINDOWS SERVICE - using a filewatcher to process submitting of .BAT files.

Worked well - lots of hurdles but nothing that was impossible to get around.

See the last post in this thread

http://www.vbforums.com/showthread.php?t=676274