Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Sets a schedule for a job.
Applies to: SQL Server (SQL Server 2008 through current version).
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:
Note that the job owner can attach a job to a schedule and detach a job from a schedule without also having to be the schedule owner. However, a schedule cannot be deleted if the detach would leave it with no jobs, unless the caller is the schedule owner.
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
SQL Server checks if the user owns both the job and the schedule.
The following example creates a schedule named NightlyJobs. Jobs that use this schedule execute every day when the time on the server is 01:00. The example attaches the schedule to the job BackupDatabase and the job RunReports.
This example assumes that the job BackupDatabase and the job RunReports already exist.
USE msdb ; GO EXEC sp_add_schedule @schedule_name = N'NightlyJobs' , @freq_type = 4, @freq_interval = 1, @active_start_time = 010000 ; GO EXEC sp_attach_schedule @job_name = N'BackupDatabase', @schedule_name = N'NightlyJobs' ; GO EXEC sp_attach_schedule @job_name = N'RunReports', @schedule_name = N'NightlyJobs' ; GO