sp_update_schedule#

Changes the settings for a schedule.

Syntax#

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 ]  /* not supported */
  [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ]  /* not supported */
  [ , [ @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 ]  /* internal */

Arguments#

[ @schedule_id = ] schedule_id

The identifier of the schedule to modify. schedule_id is int, with no default. Either schedule_id or schedule_name must be specified.

[ @name = ] ‘schedule_name’

The name of the schedule to modify. schedule_name is sysname, with no default. Either schedule_id or schedule_name must be specified.

[ @new_name = ] new_name

The new name for the schedule. new_name is sysname, with a default of NULL. When new_name is NULL, the name of the schedule is unchanged.

[ @enabled = ] enabled

Indicates the current status of the schedule. enabled is tinyint, with a default of 1 (enabled). If 0, the schedule is not enabled. When the schedule is not enabled, no jobs will run on this schedule.

[ @freq_type = ] freq_type

A value indicating when a job is to be executed. freq_type is int, with a default of 0, and can be one of these values.

Value	Description
1	Once
4	Daily
8	Weekly
16	Monthly
32	Monthly, relative to freq interval
64	Run when SQLServerAgent service starts
128	Run when the computer is idle

[ @freq_interval = ] freq_interval

The days that a job is executed. freq_interval is int, with a default of 1, and depends on the value of freq_type.

Value of freq_type	Effect on freq_interval
1 (once)	freq_interval is unused.
4 (daily)	Every freq_interval days.
8 (weekly)	freq_interval is one or more of the following (combined with an OR logical operator): 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday
16 (monthly)	On the freq_interval day of the month.
32 (monthly relative)	freq_interval is one of the following: 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday, 8 = Day, 9 = Weekday, 10 = Weekend day
64 (when service starts)	freq_interval is unused.
128	freq_interval is unused.

[ @freq_subday_type = ] freq_subday_type

Specifies the units for freq_subday_interval. freq_subday_type is int, with a default of 0, and can be one of these values.

Value	Description (unit)
0x1	At the specified time
0x2	Seconds
0x4	Minutes
0x8	Hours

[ @freq_subday_interval = ] freq_subday_interval

The number of freq_subday_type periods to occur between each execution of a job. freq_subday_intervalis int, with a default of 0.

[ @freq_relative_interval = ] freq_relative_interval

A job’s occurrence of freq_interval in each month, if freq_interval is 32 (monthly relative). freq_relative_interval is int, with a default of 0, and can be one of these values. freq_relative_interval is ignored in those cases where freq_type is not equal to 32.

Value	Description (unit)
1	First
2	Second
4	Third
8	Fourth
16	Last

[ @freq_recurrence_factor = ] freq_recurrence_factor

The number of weeks or months between the scheduled execution of a job. freq_recurrence_factor is used only if freq_type is 8, 16, or 32. freq_recurrence_factor is int, with a default of 0.

[ @active_start_date = ] active_start_date

The date on which execution of a job can begin. active_start_date is int, with a default of NULL, which indicates today’s date. The date is formatted as YYYYMMDD. If active_start_date is not NULL, the date must be greater than or equal to 19900101.

After the schedule is created, review the start date and confirm that it is the correct date.

For weekly or monthly schedules, scheduler ignores if _active_start_date) is in the past, and instead uses the current date. When a schedule is created using sp_add_schedule there is an option to specify the parameter active_start_date that is the date that job execution will begin. If the schedule type is weekly or monthly and the active_start_date parameter is set to a date in the past, the active_start_date parameter is ignored and the current date will be used for active_start_date.

After the schedule is created, review the start date and confirm that it is the correct date.

[ @active_end_date = ] active_end_date

The date on which execution of a job can stop. active_end_date is int, with a default of 99991231, which indicates December 31, 9999. Formatted as YYYYMMDD.

[ @active_start_time = ] active_start_time

The time on any day between active_start_date and active_end_date to begin execution of a job. active_start_time is int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS.

[ @active_end_time = ] active_end_time

The time on any day between active_start_date and active_end_date to end execution of a job. active_end_time is int, with a default of 235959, which indicates 11:59:59 P.M. on a 24-hour clock, and must be entered using the form HHMMSS.

[ @owner_login_name = ] ‘owner_login_name’

The name of the server principal that owns the schedule. owner_login_name is sysname, with a default of NULL, which indicates that the schedule is owned by the creator.

[ @automatic_post = ] automatic_post

Reserved.

Remarks#

All jobs that use the schedule immediately use the new settings. However, changing a schedule does not stop jobs that are currently running.

Permissions#

By default, members of the sysadmin fixed server role can execute this stored procedure.

Only members of sysadmin can modify a schedule owned by another user.

Examples#

The following example changes the enabled status of the NightlyJobs schedule to 0 and sets the owner to terrid.

EXEC dbo.sp_update_schedule
    @name = 'NightlyJobs',
    @enabled = 0,
    @owner_login_name = 'terrid';

sp_update_schedule

Contents