Post reply

SQL Scheduling Jobs

  • July 21, 2009 at 10:56 am

    #155994

    Hello all,

    The situation is as below, I would like to schedule the SQL jobs to run 3 steps

    (1) Step 1 run for just one time starting from 8am,once Step1 succeed, proceed to step2

    (2) Once Step2 succeed, proceed to Step3.

    (3) Once Step3 succeed, proceed to Step2 again.

    The 2nd and 3rd steps will keep running for 1 day from the moment where 1st step succeed till 11 59pm.

    How can I schedule the job in order for it to start again 8am next day from step1?

    Thanks for sharing.

  • July 21, 2009 at 11:02 am

    #1027807

    How about trying to execute the job with t-sql using if/else or case statements.Just a guess.

  • July 21, 2009 at 6:53 pm

    #1028049

    Thanks for the comment.

    I need more comment from you guys

    Thanks

  • July 21, 2009 at 8:02 pm

    #1028056

    golden (7/21/2009)

    Hello all,

    The situation is as below, I would like to schedule the SQL jobs to run 3 steps

    (1) Step 1 run for just one time starting from 8am,once Step1 succeed, proceed to step2

    (2) Once Step2 succeed, proceed to Step3.

    (3) Once Step3 succeed, proceed to Step2 again.

    The 2nd and 3rd steps will keep running for 1 day from the moment where 1st step succeed till 11 59pm.

    How can I schedule the job in order for it to start again 8am next day from step1?

    Thanks for sharing.

    I believe you can accomplish this with three jobs and a bit of creativity...

    Job A

    Runs each day at 8:00 AM and contains two steps. Step 1 runs your "Step 1" from the original post. Upon completion of Step 1, a second step is called. That second step is a T-SQL statement using sp_start_job that starts Job B. See http://msdn.microsoft.com/en-us/library/ms186757.aspx for details.

    Job B

    Has no schedule defined. It only runs when (Job A - Step 2) starts it. This job has two steps. Step 1 is your "Step 2" from the original post. Upon completion the job goes onto Step 2 which is your "Step 3" from the original post. Upon completion fo Step 2 the job loops back to Step 1.

    Job C

    This job runs each day at 11:59 PM. It executes a T-SQL statement, sp_stop_job, that stops Job B. More information on that procedure can be found here http://msdn.microsoft.com/en-us/library/ms182793(SQL.90).aspx

    The next day everything starts back up again at 8:00 AM.

  • July 21, 2009 at 8:37 pm

    #1028062

    Hi

    Thanks a lot for the information details.

    Can you give me some advice for this design?

    1 job contain 3 steps:

    ***(schedule to start at every 8am daily and end 11:59pm, recur every 16 hours)***

    Step1: Once succeed, goto next step

    Step2: Once succeed, goto 3rd step

    Step3: Once succeed, goto 2nd step

    Kindly advice.

    Best Regards,

    Golden

  • July 21, 2009 at 10:38 pm

    #1028084

    How this will recur every 16 hours when you are asking this to execute at some specific time everyday? You need to have at least two jobs to perform this task.

    Job 1: Starts at 8 AM everyday. This kicks off job 2.

    JOb 2: This runs continuously till 11:59 PM.

    Next day your Job 1 is kicked off by the scheduler again. No need to use three jobs.

    -LK

  • July 22, 2009 at 12:55 am

    #1028103

    Sorry,

    Mistake from me.

    Anyhow,

    If two jobs are set, then how about the starting time of the 2nd job since it is triggered by 1st job?

    Regards,

    Golden

  • July 22, 2009 at 1:20 am

    #1028113

    Job 1, on success, kicks off job 2. No need to worry for the time when job 2 is kicked off. This totally depends on job 1's execution.

    -LK

  • July 22, 2009 at 1:27 am

    #1028117

    Hi LK,

    Thanks a lot for the sharing.

    Aside to chris.schau & dba_pkashyap,

    Thanks a alot to the information as well.

    Best Regards,

    Golden

  • July 22, 2009 at 8:45 am

    #1028364

    luckysql.kinda (7/21/2009)

    How this will recur every 16 hours when you are asking this to execute at some specific time everyday? You need to have at least two jobs to perform this task.

    Job 1: Starts at 8 AM everyday. This kicks off job 2.

    JOb 2: This runs continuously till 11:59 PM.

    Next day your Job 1 is kicked off by the scheduler again. No need to use three jobs.

    -LK

    If Job 2 is looping through the two steps continuously, how will it stop at 11:59 PM? I think that chris.schau's idea of having a third job to stop the second one is the only way to do this.

    Greg

  • July 22, 2009 at 9:12 am

    #1028397

    Right. The way I understand it, setting a stop criteria in a job schedule for 11:59 can only be done if you specify a frequency for the job to run (i.e. every 1 minute). That doesn't really satisfy the requirements of the original question since it has to run continuously. It's also not an option since Job B needs to be started as a result of Job A completing and therefore can have no schedule bound to it.

  • July 22, 2009 at 9:58 am

    #1028463

    Hi chris.schau,

    Thanks again for your opinion.

    It means that if the if Job B is bound to the completion of Job A, even we set the schedule inside job B, will it cause a failure?

    Another scenario, if we set the "bound" job B to recur every 5 minutes from 8am to 11:59pm , will this job B stop exactly at 11:59pm?

    If not, what can we do if we want the Job B to recur every 5 minutes until 11:59 pm provided that Job B is still bound to Job A

    Regards,

    Golden

  • July 22, 2009 at 12:31 pm

    #1028596

    It means that if the if Job B is bound to the completion of Job A, even we set the schedule inside job B, will it cause a failure?

    You can assign a schedule to Job B but that goes against your original requirement of having Job B start as a result of Job A completing. I'm pretty certain SQL Server Agent cannot start a job that is already running so the second step of Job A (the step that starts Job B) will fail when it tries to start Job B due to the fact that Job B is already running according to your newly assigned schedule.

    Another scenario, if we set the "bound" job B to recur every 5 minutes from 8am to 11:59pm , will this job B stop exactly at 11:59pm?

    No. I believe the "11:59" means "Don't start (recur) this job if it is past 11:59." The problem in your scenario is that your job never stops because the steps loop. Since your job never stops the "recur every 5 minutes" never applies because as I mentioned above, you can't start a job that is already running.

    Hope this helps.

  • July 22, 2009 at 8:27 pm

    #1028739

    Hi Chris

    As in your reply, we need to stop Job B by creating Job C since it is bound to the completion of Job A.

    If I would like to set my Job B to recur every 5 minutes until 11:59pm after the completion of Job A.

    What can I do for Job C since Job B is not scheduled?

    Thanks and regards,

    Golden

  • July 23, 2009 at 5:38 am

    #1028865

    Appreciate if somebody can advice me.

    Thanks and regards,

    Golden

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply