sp_add_jobschedule (Transact-SQL)

 

Updated: July 28, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Creates a schedule for a job.

Topic link icon Transact-SQL Syntax Conventions

  
sp_add_jobschedule [ @job_id = ] job_id, | [ @job_name = ] 'job_name', [ @name = ] 'name'  
     [ , [ @enabled = ] enabled_flag ]  
     [ , [ @freq_type = ] frequency_type ]  
     [ , [ @freq_interval = ] frequency_interval ]  
     [ , [ @freq_subday_type = ] frequency_subday_type ]  
     [ , [ @freq_subday_interval = ] frequency_subday_interval ]  
     [ , [ @freq_relative_interval = ] frequency_relative_interval ]  
     [ , [ @freq_recurrence_factor = ] frequency_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 ]  
     [ , [ @schedule_id = ] schedule_id OUTPUT ]  

[ @job_id= ] job_id
Job identification number of the job to which the schedule is added. job_id is uniqueidentifier, with no default.

[ @job_name= ] 'job_name'
Name of the job to which the schedule is added. job_name is nvarchar(128), with no default.

System_CAPS_ICON_note.jpg Note


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

[ @name= ] 'name'
Name of the schedule. name is nvarchar(128), with no default.

[ @enabled= ] enabled_flag
Indicates the current status of the schedule. enabled_flag 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= ] frequency_type
Value that indicates when the job is to be executed. frequency_type is int, with a default of 0, and can be one of the following values:

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

[ @freq_interval= ] frequency_interval
Day that the job is executed. frequency_interval is int, with a default of 0, and depends on the value of frequency_type as indicated in the following table:

ValueEffect
1 (once)frequency_interval is unused.
4 (daily)Every frequency_interval days.
8 (weekly)frequency_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 frequency_interval day of the month.
32 (monthly relative)frequency_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 the SQL Server Agent service starts)frequency_interval is unused.
128frequency_interval is unused.

[ @freq_subday_type= ] frequency_subday_type
Specifies the units for frequency_subday_interval. frequency_subday_type is int, with no default, and can be one of the following values:

ValueDescription (unit)
0x1At the specified time
0x4Minutes
0x8Hours

[ @freq_subday_interval= ] frequency_subday_interval
Number of frequency_subday_type periods to occur between each execution of the job. frequency_subday_interval is int, with a default of 0.

[ @freq_relative_interval= ] frequency_relative_interval
Further defines the frequency_interval when frequency_type is set to 32 (monthly relative).

frequency_relative_interval is int, with no default, and can be one of the following values:

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

frequency_relative_interval indicates the occurrence of the interval. For example, if frequency_relative_interval is set to 2, frequency_type is set to 32, and frequency_interval is set to 3, the scheduled job would occur on the second Tuesday of each month.

[ @freq_recurrence_factor= ] frequency_recurrence_factor
Number of weeks or months between the scheduled execution of the job. frequency_recurrence_factor is used only if frequency_type is set to 8, 16, or 32. frequency_recurrence_factor is int, with a default of 0.

[ @active_start_date= ] active_start_date
Date on which job execution can begin. active_start_date is int, with no default. The date is formatted as YYYYMMDD. If active_start_date is set, 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 more information, see the section "Scheduling Start Date" in Create and Attach Schedules to Jobs.

[ @active_end_date= ] active_end_date
Date on which job execution can stop. active_end_date is int, with no default. The date is formatted as YYYYMMDD.

[ @active_start_time= ] active_start_time
Time on any day between active_start_date and active_end_date to begin job execution. active_start_time is int, with no default. The time is formatted as HHMMSS on a 24-hour clock.

[ @active_end_time=active_end_time
Time on any day between active_start_date and active_end_date to end job execution. active_end_time is int, with no default. The time is formatted as HHMMSS on a 24-hour clock.

[ @schedule_id=schedule_idOUTPUT
Schedule identification number assigned to the schedule if it is created successfully. schedule_id is an output variable of type int, with no default.

[ @schedule_uid= ] schedule_uidOUTPUT
A unique identifier for the schedule. schedule_uid is a variable of type uniqueidentifier.

0 (success) or 1 (failure)

None

Job schedules can now be managed independently of jobs. To add a schedule to a job, use sp_add_schedule to create the schedule and sp_attach_schedule to attach the schedule to a job.

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

The following example assigns a job schedule to SaturdayReports which will execute every Saturday at 2:00 AM.

EXEC msdb.dbo.sp_add_jobschedule 
        @job_name = N'SaturdayReports', -- Job name
        @name = N'Weekly_Sat_2AM',  -- Schedule name
        @freq_type = 8, -- Weekly
        @freq_interval = 64, -- Saturday
        @freq_recurrence_factor = 1, -- every week
        @active_start_time = 20000 -- 2:00 AM

Create and Attach Schedules to Jobs
Schedule a Job
Create a Schedule
SQL Server Agent Stored Procedures (Transact-SQL)
sp_add_schedule (Transact-SQL)
sp_update_schedule (Transact-SQL)
sp_delete_schedule (Transact-SQL)
sp_help_schedule (Transact-SQL)
sp_attach_schedule (Transact-SQL)

Community Additions

ADD
Show: