If you’re in the process of creating a SQL Server Agent job with T-SQL, you may be wondering whether to create its schedule with the sp_add_schedule stored procedure or the sp_add_jobschedule procedure.

Here’s a quick explanation of the difference between these two procedures.

The Difference

• The sp_add_schedule procedure creates a schedule, but doesn’t attach it to any jobs. To do that, you need to use the sp_attach_schedule procedure.
• The sp_add_jobschedule procedure creates the schedule and attaches it to a given job, all in one go.

Therefore, sp_add_jobschedule is OK to use if you need to create a new schedule for a specific job, and that job already exists. If you’re creating a schedule for multiple jobs, and none of them exist yet, you can use sp_add_schedule. Then you can use sp_attach_schedule to attach it to your jobs later.

But that’s not to say that you can’t use sp_add_schedule instead of sp_add_jobschedule if the job already exists.

Using sp_add_schedule (in conjunction with sp_attach_schedule) allows you to separate the process of creating schedules and attaching them to jobs. You can still use this combo even when creating a single job with a single schedule.

In other words, you can use sp_add_schedule instead of sp_add_jobschedule for all your SQL Server Agent jobs.

Example

The sp_add_jobschedule Procedure

The sp_add_jobschedule procedure creates a schedule for a given SQL Server Agent job. When you call this procedure, you must provide the name or ID of a job that you want it to apply to.

Basically, this procedure creates a schedule and attaches it to a single job.

Here’s an example of creating a job and using the sp_add_jobschedule stored procedure:

EXEC sp_add_jobschedule 
    @job_name = N'BackupMusicDB',
    @name = N'Weekly_Sun_3AM',
    @freq_type = 8,
    @freq_interval = 1,
    @freq_recurrence_factor = 1,
    @active_start_time = 30000;

When you look at the sp_add_jobschedule procedure, the @job_name argument provides the job name. The @name argument provides the name that you want to give to the schedule.

The sp_add_schedule Procedure

The sp_add_schedule procedure allows you to create a schedule without having to specify a job to attach it to.

This procedure creates the schedule, but it doesn’t attach it to any jobs. To attach it to a job, you must use the sp_attach_schedule procedure.

Here’s an example:

EXEC sp_add_schedule 
    @schedule_name = N'RunDailyAM',
    @freq_type = 4,
    @freq_interval = 4,
    @active_start_time = 041000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'BackupPetHotelDB',  
   @schedule_name = N'RunDailyAM';  
GO

When you use sp_attach_schedule, you must provide the name of the job or its ID (but not both), as well as the schedule name or ID (but not both).

Attaching an sp_add_jobschedule Schedule

It’s important to note that sp_attach_schedule can attach schedules that were created with both sp_add_schedule and with sp_add_jobschedule.

In other words, creating a schedule with sp_add_jobschedule does not restrict it to just the job you specified when creating the schedule. it is still available to be attached to other jobs later using sp_attach_schedule.