Page 1 of 2 12 LastLast
Results 1 to 40 of 53

Thread: Can Window Service Call a Stored Procedure?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    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!

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Can Window Service Call a Stored Procedure?

    where are you trying to write the file to?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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,

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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!

  9. #9
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Can Window Service Call a Stored Procedure?

    sql Code:
    1. 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.

  10. #10
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Can Window Service Call a Stored Procedure?

    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?
    Last edited by SJWhiteley; Feb 22nd, 2012 at 04:04 PM.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by 9pepper View Post
    ...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??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

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

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

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

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  20. #20
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by szlamany View Post
    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????

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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)

  22. #22
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  23. #23

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  24. #24
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by szlamany View Post
    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  25. #25

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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!

  26. #26
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  29. #29
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by szlamany View Post
    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

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

  31. #31
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by 9pepper View Post
    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 View Post
    Ok - change it to this

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  32. #32

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

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

  33. #33

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    I will be back in an hour.

  34. #34
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can Window Service Call a Stored Procedure?

    Quote Originally Posted by 9pepper View Post
    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  35. #35
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Can Window Service Call a Stored Procedure?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  36. #36

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  37. #37

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

  38. #38

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

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

  39. #39
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Can Window Service Call a Stored Procedure?

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  40. #40

    Thread Starter
    Junior Member
    Join Date
    Feb 2012
    Posts
    26

    Re: Can Window Service Call a Stored Procedure?

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width