So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for each schedule type.
Is this possible?
2
-
Can you give more details, e.g. are the schedules all on the top of the hour, do any of the procedures to run during more than one of the time slots, etc.– Aaron BertrandCommented Jan 28, 2014 at 19:35
-
Sure. Basically I have a number of jobs that run according to various schedules and would like to consolidate these jobs into one. The schedules of the aforementioned jobs may run during more than one of the timeslots. Also, I think the schedules may all be at the top of the hour--is the there maybe a more robust solution? But anyway, hope this helps!– DrewCommented Jan 28, 2014 at 19:39
Add a comment
|
1 Answer
If the jobs are all scheduled to run at the top of the hour, then you could simply have a single job, with a single step, and schedule the job to run every hour (or on select hours). Then the step can have logic like this:
DECLARE @h INT = DATEPART(HOUR, CURRENT_TIMESTAMP);
IF @h = 1
BEGIN
EXEC dbo.ProcedureToRunAt1AM;
END
IF @h = 2
BEGIN
EXEC dbo.OneProcedureToRunAt2AM;
EXEC dbo.AnotherProcedureToRunAt2AM;
END
IF @h IN (4,16)
BEGIN
EXEC dbo.OneProcedureToRunAt4AMand4PM;
END
If you need it to be more granular, you can check the minute, minute/10, etc.
answered Jan 28, 2014 at 19:45
