Can Window Service Call a Stored Procedure?

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

[szlamany]

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.

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.

[9pepper]

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.

[szlamany]

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

[9pepper]

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!