sp_update_jobschedule

Transact-SQL Reference

Transact-SQL Reference

sp_update_jobschedule

Changes the schedule settings for the specified job.

Syntax

sp_update_jobschedule [@job_id =] job_id, | [@job_name =] 'job_name',
    [@name =] '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]

Arguments

[@job_id =] job_id

Is the identification number of the job to which the schedule belongs. job_id is uniqueidentifier, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job to which the schedule belongs. Each job name must be unique. job_name is sysname, with a default of NULL.

Note  Either job_id or job_name must be specified but both cannot be specified.

[@name =] 'name'

Is the name of the schedule to modify. name is sysname, with no default.

[@new_name =] 'new_name'

Is a new name for the schedule. new_name is sysname, with a default of NULL.

[@enabled =] enabled

Specifies whether the schedule is enabled (1) or not enabled (0). enabled is tinyint, with a default of NULL.

[@freq_type =] freq_type

Specifies how often the job is run. freq_type is int, with a default of NULL, and can be one of these values.

Value Description
1 Once.
4 Daily.
8 Weekly.
16 Monthly.
32 Monthly, relative to the freq_interval.
64 Run when SQL Server Agent starts.
128 Run when the computer is idle.

[@freq_interval =] freq_interval

Specifies the days that the job is run. freq_interval is int, with a default of NULL. The value of freq_interval 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 (ORed together):

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 can be one of these values:

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekday
10 = Weekend day

64 (when SQL Server Agent starts) 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 NULL, and can be one of these values.

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

[@freq_subday_interval =] freq_subday_interval

Specifies the number of freq_subday_type periods to occur between each execution of the job. freq_subday_interval is int, with a default of NULL.

[@freq_relative_interval =] freq_relative_interval

Specifies the scheduled job's occurrence of the freq_interval in each month, if freq_interval is 32 (monthly relative). freq_relative_interval is int, with a default of NULL, and can be one of these values.

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

[@freq_recurrence_factor =] freq_recurrence_factor

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

[@active_start_date =] active_start_date

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

[@active_end_date =] active_end_date

Is the date on which execution of the job can stop. active_end_date is int, with a default of NULL. Values must be formatted as YYYYMMDD.

[@active_start_time =] active_start_time

Is the time on any day between active_start_date and active_end_date to begin execution of the job. active_start_time is int, with a default of NULL. Values must be entered using the form HHMMSS.

[@active_end_time =] active_end_time

Is the time on any day between active_start_date and active_end_date to end execution of the job. active_end_time is int, with a default of NULL. Values must be entered using the form HHMMSS.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_update_jobschedule must be run from the msdb database.

Updating a job schedule increments the job version number.

Permissions

Execute permissions default to the public role.

Examples

This example disables and changes the name of the Monday Archive schedule of the Archive Tables job.

USE msdb
EXEC sp_update_jobschedule @job_name = 'Archive Tables', 
   @name = 'Monday Archive',
   @new_name = 'Monday Archive - DEACTIVATED',
   @enabled = 0

See Also

Modifying and Viewing Jobs

sp_add_jobschedule

sp_delete_jobschedule

sp_help_jobschedule

System Stored Procedures