SQL Scheduling Jobs

Question

Post reply

SQL Scheduling Jobs

golden-1131024

Ten Centuries

Points: 1285
More actions
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.

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

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

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 1

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

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 2

Thanks for the comment.

I need more comment from you guys

Thanks

chris.schau Mr or Mrs. 500 Points: 580 More actions · Answer 3

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.

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 4

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

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 5

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

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 6

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

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 7

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

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 8

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 9

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

chris.schau Mr or Mrs. 500 Points: 580 More actions · Answer 10

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.

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 11

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

chris.schau Mr or Mrs. 500 Points: 580 More actions · Answer 12

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.

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 13

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

golden-1131024 Ten Centuries Points: 1285 More actions · Answer 14

Appreciate if somebody can advice me.

Thanks and regards,

Golden