First thing needed is a little stored procedure for creating interval schedules.
USE msdb
GO
CREATE PROCEDURE spCreateSchedule_Interval
@scheduleName NVARCHAR(255),
@intervalType VARCHAR(255), -- one of 'seconds', 'minutes', 'hours'
@interval int,
@ScheduleId int OUT
AS
BEGIN
-- determine time interval
DECLARE @intervalTypeInt INT;
IF @intervalType = 'seconds'
SET @intervalTypeInt = 2;
ELSE IF @intervalType = 'minutes'
SET @intervalTypeInt = 4;
ELSE IF @intervalType = 'hours'
SET @intervalTypeInt = 8;
EXEC msdb.dbo.sp_add_jobschedule
@job_name='NameOfTheJobToBeApplied', -- or you can use @job_id instead
@name=@scheduleName, -- you can later find the schedule to update/delete using this name, or the @ScheduleId
@enabled=1,
@freq_type=4, -- daily
@freq_interval=1, -- every day
@freq_subday_type=@intervalTypeInt, -- eg. 2 = seconds
@freq_subday_interval=@interval, -- eg. 15 - run every 15 seconds
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160101, -- some date in the past to activate immediately, or put some date in the future for delay
@active_end_date=99991231, -- never end, or specify some valid date
@active_start_time=000000, -- active from 00:00:00 - caution: when creating multiple schedules use different time here, eg 000001, 000002, so that they not get started simultanously, as it might couse some errrors
@active_end_time=235959, -- active to 23:59:59
@schedule_id=@ScheduleID -- this will output the newly generated id, which can be used later to localize the schedule for update/delete
END;
GO
Example usage:
DECLARE @ScheduleId int;
EXEC spCreateSchedule_Interval
@scheduleName = 'UserA_Schedule',
@intervalType = 'minutes',
@interval = 27,
@ScheduleId = @ScheduleId OUT;
This should create schedule to run every 27 minutes.
You can also need a proc for creating a schedule for specific time:
CREATE PROCEDURE spCreateSchedule_ExactTime
@scheduleName NVARCHAR(255),
@timeToRun TIME,
@ScheduleId int OUT
AS
BEGIN
DECLARE @StartTime INT;
SET @StartTime = DATEPART(hour, @timeToRun) * 10000 + DATEPART(minute, @timeToRun) * 100 + DATEPART(second, @timeToRun);
EXEC msdb.dbo.sp_add_jobschedule
@job_name='NameOfTheJobToBeApplied', -- or you can use @job_id instead
@name=@scheduleName, -- you can later find the schedule to update/delete using this name, or the @ScheduleId
@enabled=1,
@freq_type=4, -- daily
@freq_interval=1, -- every day
@freq_subday_type=1, -- At the specified time
@freq_subday_interval=1, -- once a day, probably not used
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160101, -- some date in the past to activate immediately, or put some date in the future for delay
@active_end_date=99991231, -- never end, or specify some valid date
@active_start_time=@StartTime, -- active from 00:00:00 - caution: when creating multiple schedules use different time here, eg 000001, 000002, so that they not get started simultanously, as it might couse some errrors
@active_end_time=235959, -- active to 23:59:59
@schedule_id=@ScheduleID -- this will output the newly generated id, which can be used later to localize the schedule for update/delete
END;
GO
Example usage:
DECLARE @ScheduleId INT;
EXEC spCreateSchedule_ExactTime
@scheduleName = 'UserB_Schedule',
@timeToRun = '14:58:00',
@ScheduleId = @ScheduleId OUT;
This should create schedule to run every day at 14:58.
The above two procedures might be easily merged into one. Separated here for clarity and ease of maintenance.
They can also be further enhanced, you can parametrize the @freq_type, @freq_interval etc.
All you need is in the documentation: https://msdn.microsoft.com/pl-pl/library/ms366342(v=sql.110).aspx
Another step are procedures for updating existing schedules:
CREATE PROCEDURE spUpdateSchedule_Interval
@scheduleName NVARCHAR(255),
@intervalType VARCHAR(255), -- one of 'seconds', 'minutes', 'hours'
@interval int
--, @ScheduleId int -- you can use this instead of the firs param
AS
BEGIN
-- determine time interval
DECLARE @intervalTypeInt INT;
IF @intervalType = 'seconds'
SET @intervalTypeInt = 2;
ELSE IF @intervalType = 'minutes'
SET @intervalTypeInt = 4;
ELSE IF @intervalType = 'hours'
SET @intervalTypeInt = 8;
EXEC msdb.dbo.sp_update_schedule
--@schedule_id=@ScheduleID, -- you can use this instead of the line below, if you change the proc parameter
@name=@scheduleName,
--@new_name = @newName -- if you want to change the schedule name
@enabled=1,
@freq_type=4, -- daily
@freq_interval=1, -- every day
@freq_subday_type=@intervalTypeInt, -- eg. 2 = seconds
@freq_subday_interval=@interval, -- eg. 15 - run every 15 seconds
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160101, -- some date in the past to activate immediately, or put some date in the future for delay
@active_end_date=99991231, -- never end, or specify some valid date
@active_start_time=000000, -- active from 00:00:00 - caution: when creating multiple schedules use different time here, eg 000001, 000002, so that they not get started simultanously, as it might couse some errrors
@active_end_time=235959 -- active to 23:59:59
END;
GO
And the usage:
EXEC spUpdateSchedule_Interval
@scheduleName = 'UserB_Schedule',
@intervalType = 'minutes',
@interval = 25;
GO
You should now be able to create spUpdateSchedule_ExactTime by analogy.
The last thing you need - a stored procedure for deleting schedules:
USE msdb
GO
CREATE PROCEDURE spDeleteSchedule
@scheduleName VARCHAR(255)
AS
BEGIN
EXEC msdb.dbo.sp_delete_schedule @schedule_name = @scheduleName, @force_delete = 1;
END;
GO
And its usage:
USE msdb
GO
EXEC spDeleteSchedule 'UserA_Schedule';
Or you can easily write alternative which will use schedule_id instead of schedule_name (sp_delete_schedule can get either of those).
NOTICE:
In updating and deleting procedures you can use either names or IDs for identifying schedules.
While names are more human friendly, and I used them for the examples to be easier to follow, I strongly recommend that you use IDs instead.
Names are not forced to be unique, so if you happen to create two schedules with the same name, then both delete and update procs will fail, unless you use schedule_id as a parameter.
