4

I have a set of SQL server jobs and I want the schedule for them to be dynamic i.e. I want the next run date to come from a table.

I have tried updating next_run_date in the sysjobschedules table and next_scheduled_run_date in sysjobactivity table but this doesn't do anything.

How would I go about this?

evoandy
  • 630
  • 4
  • 16
  • 31

1 Answers1

3

I think you can use - sp_update_schedule to update the schedule. 

sp_update_schedule 
    {   [ @schedule_id = ] schedule_id 
      | [ @name = ] 'schedule_name' }
    [ , [ @new_name = ] new_name ]
    [ , [ @enabled = ] enabled ]
    [ , [ @freq_type = ] freq_type ]
    [ , [ @freq_interval = ] freq_interval ] 
    [ , [ @freq_subday_type = ] freq_subday_type ] 
    [ , [ @freq_subday_interval = ] freq_subday_interval ] 
    [ , [ @freq_relative_interval = ] freq_relative_interval ] 
    [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ] 
    [ , [ @active_start_date = ] active_start_date ] 
    [ , [ @active_end_date = ] active_end_date ] 
    [ , [ @active_start_time = ] active_start_time ] 
    [ , [ @active_end_time = ] active_end_time ] 
    [ , [ @owner_login_name = ] 'owner_login_name' ]
    [ , [ @automatic_post =] automatic_post ]
AgentSQL
  • 2,810
  • 20
  • 22
  • I looked at this sp but it doesn't have next run date. If I use this would it be like creating a new schedule every time i.e. changing the active start date? – evoandy Aug 19 '13 at 13:29
  • Updating `next_run_date` in `sysjobschedules` will not solve question. The `sysjobschedules` table refreshes every 20 minutes. It depends on various of other fields - `freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, active_start_date, active_start_time`. So, you must reference these fields to change next run date. – AgentSQL Aug 19 '13 at 13:45
  • Visit [this](http://sqlmag.com/t-sql/plugging-gaps-sql-server-job-tracking) site for more information. – AgentSQL Aug 19 '13 at 13:46