none
How to wait for event to finish execution?

    Question

  • This might sound really basic, and for most other programming languages would know what to do, but I am only new to SQL Server so please state the obvious...

    I have a stored proc that runs overnight on a schedule, no problems.  I have also given a user the ability to  run this SP via an Excel link using

    EXEC msdb.dbo.sp_start_job '<SQL job name>'

    The problem, I would like the code to wait for the <SQL Job> to be completed before returning that everything is finished.  I was think something like

    declare @jid uniqueidentifier

    declare @Complete integer

    EXEC msdb.dbo.sp_start_job '<SQL job name>' @job_id = @jid

    Set @Complete = (Select "status" from "some_system_table" where job_ID = @jid)

    While @complete <> "Complete"

        Set @Complete = (Select "status" from "some_system_table" where job_ID = @jid)

    End

     

    The trouble is I dont know the exact syntax or system tables to use.  I have had a look through the forum and cannot find anything specific, just a lot that is very close.

     

    Can anyone please point me in the right direction?

     

    Coz...

     

    Saturday, January 24, 2009 3:46 AM

Answers

  • As I see it, the issue with using a stored procedure to run multiple job is this: The muliple jobs may be execute in parallel, and there is no guarentee that the email notification will not happen before the jobs are complete. EXECUTE 'job' just starts the job.

    You really don't want to create artificial waits in your procedure.

    Create one job that is started by the users. That one job can have multiple steps. One step can be sending a email notification using dbmail. Each step will wait for completion of the previous steps before beginning.

    You can even use a step to start another job.


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by Coz4223 Sunday, January 25, 2009 3:47 AM
    Saturday, January 24, 2009 10:19 PM

All replies

  • Well, I'm not a sql expert. I'm learning as well, but I've spent many years on the end of a workbook. :)

    ...and if they're executing a command object through Excel, it won't return 'until' the stored procedure is done executing unless you throw a doevents in there somewhere.

    So, in closing, it 'will' wait, by nature, until the job is done.

    Are you not experiencing this?

    Edit: I believe your question is more Excel VBA related than T-SQL.

    Learning SQL

    Saturday, January 24, 2009 5:23 AM
  • Hello, Coz

    You can find out which jobs are currently running by using:

    EXEC msdb.dbo.sp_help_job @execution_status = 1

    Saturday, January 24, 2009 6:05 AM
  • Typically, a step is added that will send an email message upon completion, indicating either success or failure.

    See the advance tab on the Job Step.


    You may be only one person in the world, but you may also be the world to one person.
    Saturday, January 24, 2009 7:35 AM
  • Yes, I want to send the user an email when the task is completed.  I have used the notificiation in the Job Step to send an email to myself should the job fail and I cannot see the ability to set up a second notification.  Therefore, I wanted to use a "sendmail" in SQL of Stored Proc I am calling from Excel.

    Along the lines of:

    Exec "SQL Job 1"

    Exec "SQL Job 2"

    While "not completed"

        wait...

    End

    send mail "to user"

    Saturday, January 24, 2009 9:15 PM
  • You will have to handle this in the SP.. I would recommend that you make 2 SPs. One having a Sendmail to User (which you can get from users login) .. when you run this SP the job notification will come to you and the XP_sendmail in the Proceedure will send mail to users on completion.

    the other SP,run by the scheduler can be without XP_sendmail .

     

    hope this helps


    Ashwani Roy - MCITP http://ashwaniroy.spaces.live.com/
    Saturday, January 24, 2009 9:58 PM
  • As I see it, the issue with using a stored procedure to run multiple job is this: The muliple jobs may be execute in parallel, and there is no guarentee that the email notification will not happen before the jobs are complete. EXECUTE 'job' just starts the job.

    You really don't want to create artificial waits in your procedure.

    Create one job that is started by the users. That one job can have multiple steps. One step can be sending a email notification using dbmail. Each step will wait for completion of the previous steps before beginning.

    You can even use a step to start another job.


    You may be only one person in the world, but you may also be the world to one person.
    • Marked as answer by Coz4223 Sunday, January 25, 2009 3:47 AM
    Saturday, January 24, 2009 10:19 PM
  • Thanks all,

    A fresh view was just what I needed.  I have create a second job now that runs the multiple jobs and added a notification at the end.  I am looking into send mail now to add that last step you speak of.

    Thanks again, Coz.

    Sunday, January 25, 2009 3:46 AM
  • I have just changed my SP to steps in a SQL Agent Job, but it still doesnt wait for the compeltion of the queries before starting the next step.  Am I missing something?

    Each step has an SQL statement and in full looks like:

    1. Truncate Log file

    2. Load Start Time of upload

    3. EXEC msdb.dbo.sp_start_job 'Job Name 1'

    4. EXEC msdb.dbo.sp_start_job 'Job Name 2'

    5. Load Finish Time of upload

    6. Send email notification

     

    The log times of start and finish are exactly the same and the email notification turns up immmediately.  When I manually check the query it is still running and takes around 10 minutes to finish.

    Does this sound correct?

    Coz.

    Sunday, January 25, 2009 4:13 AM
  • What you report is the correct behavior. Using 'sp_start_job' begins an async activity -the job starting is step completion.

    If you want to create a controlled 'flow', where each step completes before the next one begins, then you cannot just use sp_start_job to start a job, you will have to have the complete action as the job step.


    You may be only one person in the world, but you may also be the world to one person.
    Sunday, January 25, 2009 8:44 AM