Sp_add_jobschedule (TRANSACT-SQL) create a job plan

Sp_add_jobschedule (TRANSACT-SQL)

New Date:April 14, 2006

Create a job plan.

Syntax

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 ]

Parameters

[@ Job_id =] job_id

Add the job ID of the scheduled job. The data type of job_id is uniqueidentifier, and no default value is set.

[@ Job_name =] 'job _ name'

Name of the job to add the scheduler. The data type of job_name is nvarchar (128) and no default value is set.

Note:
You must specify job_id or job_name, but not both.

[@ Name =] 'name'

The name of the scheduler. The data type of name is nvarchar (128), with no default value.

[@ Enabled =] enabled_flag

Indicates the current status of the plan. The data type of enabled_flag is tinyint. The default value is 1 (Enabled ). If the value is 0, the scheduler is not enabled. When this plan is disabled, the job will not run.

[@ Freq_type =] frequency_type

Specifies the job execution time. The data type of frequency_type is int, and the default value is 0. It can be one of the following values:

Value	Description
1	Once
4	Every day
8	Weekly
16	Monthly
32	Monthly, relative to frequency_interval.
64	Run when the SQL Server proxy service is started.
128	Run when the computer is idle.

[@ Freq_interval =] frequency_interval

The date on which the job is executed. The data type of frequency_interval is int and the default value is 0. It depends on the frequency_type value indicated in the following table:

Value	Result
1 (once)	Do not use frequency_interval.
4 (daily)	Every frequency_interval day.
8 (per week)	Frequency_interval is one or more of the following values (combined with the logical operator or ): 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday
16 (per month)	The frequency_interval day of each month.
32 (related to the "Monthly" option)	Frequency_interval is one of the following values: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = workday 10 = day off
64 (when the SQL Server proxy service is started)	Do not use frequency_interval.
128	Do not use frequency_interval.

[@ Freq_subday_type =] frequency_subday_type

Specifies the unit of frequency_subday_interval. The data type of frequency_subday_type is int. It has no default value and can be one of the following values:

Value	Description (unit)
0x1	At the specified time
0x4	Minute
0x8	Hour

[@ Freq_subday_interval =] frequency_subday_interval

Number of frequency_subday_type cycles between two jobs. The data type of frequency_subday_interval is int, and the default value is 0.

[@ Freq_relative_interval =] frequency_relative_interval

When frequency_type is set to 32 (related to the "Monthly" option), this parameter further defines frequency_interval.

The data type of frequency_relative_interval is int. It has no default value and can be one of the following values:

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

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 will occur on the second Tuesday of each month.

[@ Freq_recurrence_factor =] frequency_recurrence_factor

The number of weeks or months between two planned executions. Frequency_recurrence_factor is used only when frequency_type is set to 8, 16, or 32. The data type of frequency_recurrence_factor is int, and the default value is 0.

[@ Active_start_date =] active_start_date

The date on which the job can be started. The data type of active_start_date is int, with no default value. The date format is yyyymmdd. If active_start_date is set, the date must be greater than or equal to 19900101.

[@ Active_end_date =] active_end_date

The date on which the job can be stopped. The data type of active_end_date is int, with no default value. The date format is yyyymmdd.

[@ Active_start_time =] active_start_time

The time when the job starts to be executed on any date between active_start_date and active_end_date. The data type of active_start_time is int, with no default value. The time format is hhmmss, in the 24-hour format.

[@ Active_end_time = active_end_time

The time when the job is stopped on any date between active_start_date and active_end_date. The data type of active_end_time is int, with no default value. The time format is hhmmss, in the 24-hour format.

[@ Schedule_id = schedule_id output

The plan ID assigned to the plan when the plan is created successfully. Schedule_id is an output variable of the int type and has no default value.

Remarks

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

Return code value

0 (successful) or 1 (failed)

Result set

None

Permission

By default, only members of the SysAdmin fixed server role can execute this stored procedure. Other users must be granted one of the following permissions for the SQL Server proxy fixed database role in the MSDB database:

• Sqlagentuserrole
  
• Sqlagentreaderrole
  
• Sqlagentoperatorrole
  

For more information about the permissions of these roles, see SQL Server proxy fixed database roles.