Can Window Service Call a Stored Procedure?

[9pepper]

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!

[techgnome]

where are you trying to write the file to?

-tg

[szlamany]

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.

[9pepper]

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

[szlamany]

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

Try this

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

[9pepper]

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,

[szlamany]

Ok - create a table called DebugSproc_T

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

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

[9pepper]

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!

[Niya]

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.

[techgnome]

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

[9pepper]

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.

[9pepper]

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.

[9pepper]

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

[techgnome]

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

[9pepper]

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.

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

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

[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

[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