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.

[SJWhiteley]

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?

[szlamany]

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

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.

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

[9pepper]

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.

[szlamany]

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

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

[9pepper]

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.

[szlamany]

Ok - change it to this

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

[9pepper]

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

[szlamany]

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

[9pepper]

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

[9pepper]

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.

[szlamany]

Ok - change it to this

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

[9pepper]

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)

[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

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!

[9pepper]

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.

[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

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

[9pepper]

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!

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

[9pepper]

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.

[9pepper]

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.

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

[9pepper]

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

[szlamany]

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

Ok - change it to this

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

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

[9pepper]

I will be back in an hour.

[szlamany]

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

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