Sql-server – Can you Schedule a job inside another job

jobssql-server-2008t-sql

I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a locking situation where two jobs are waiting for each other to finish.

The built in retry wont work because they are considered running while waiting to retry. I could just error out and have the job run every 10min checking when it was last run successfully and if anyone else is running. Then run or error again, but that could lead to jobs happening way off scheduled.

I have looked around but can't find anything, so I don't know if it is even possible but can you set up a one time schedule, like run this job at this time from inside another job?

Or is there a better way of doing this that I have overlooked?

Best Answer

In more research and diving into the msdn site documentation I have found and am now using.

sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule

so when the job sees that others are running ATM I am doing this:

exec msdb.dbo.sp_add_schedule [MSDN link][1]
   @schedule_name = @TempScheduleName,
   @enabled = 1,
   @freq_interval = 1,
   @freq_type = 1,
   @freq_subday_type = 4,
   @freq_recurrence_factor = 1,
   @freq_subday_interval = 5,
   @active_start_date = @todaysdate,
   @active_start_time = @todaystime (plus 5min)


exec msdb.dbo.sp_attach_schedule [MSDN Link][2]
   @job_name = @JobName',
   @schedule_name = @TempScheduleName

then the next time the job is run this is also done to remove the temp schedule:

   exec msdb.dbo.sp_detach_schedule
    @job_name = @JobName,
    @schedule_name = @TempScheduleName

   exec msdb.dbo.sp_delete_schedule
   @schedule_name = @TempScheduleName
Related Question