Can Window Service Call a Stored Procedure?

[9pepper]

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.

[techgnome]

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

-tg

[9pepper]

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

[szlamany]

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??

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

[techgnome]

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

[szlamany]

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

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?

[techgnome]

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

[szlamany]

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