sp_add_jobschedule (T-SQL)

Creates a schedule for a job.

Syntax

sp_add_jobschedule [@job_id =] job_id, | [@job_name =] 'job_name',
    
[@name =] '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
[@jobid =] job_id
Is the job identification number of the job to which the schedule is added. job_id is uniqueidentifier, with a default of NULL.
[@job_name =] 'job_name'
Is the name of the job to which added the schedule is added. 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. name is sysname, with no default.
[@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 disabled, the job will not be run.
[@freq_type =] freq_type
Is a value indicating when the 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 SQL Server Agent starts.
128 Run when the computer is idle.

[@freq_interval =] freq_interval
Is the days that the job is executed. freq_interval is int, with a default of 0, 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 (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 is one of the following:
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Week day
10 = Weekend day
64 (When SQL Server Agent starts) freq_interval is unused.
128 freq_interval is unused.

[@freq_subday_type =] freq_subday_type
Is 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
0x4 Minutes
0x8 Hours

[@freq_subday_interval =] freq_subday_interval
Is the number of freq_subday_type periods to occur between each execution of the job. freq_subday_interval is int, with a default of 0.
[@freq_relative_interval =] freq_relative_interval
Is the scheduled 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.

 

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

[@freq_recurrence_factor =] freq_recurrence_factor
Is the number of weeks or 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 0.
[@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, 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.
[@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 99991231, which indicates December 31, 9999. 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 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
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 235959, which indicates 11:59:59 P.M. on a 24-hour clock, and must be entered using the form HHMMSS.
Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Permissions

Execute permissions default to the public role.

Examples

This example assumes the job NightlyBackup has been created to back up a database. It adds the job to the schedule with the name ScheduledBackup and executes every day at 1:00 A.M.

USE msdb

EXEC sp_add_jobschedule @job_name = 'NightlyBackup',

    @name = 'ScheduledBackup',
    @freq_type = 4, -- daily
    @freq_interval = 1,
    @active_start_time = '1:00:00'

  

See Also
Modifying and Viewing Jobs sp_update_jobschedule
sp_delete_jobschedule System Stored Procedures
sp_help_jobschedule  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.