Schedule SQL jobs at user time intervals every day

Question

In my application (ASP.NET, C #), I need to run the stored procedure every time in a set of predefined time intervals. So I created a sql job and paid the same. But the problem is that it is possible to create / modify these time intervals using the application, and this will save the changed time intervals in the table. Therefore, I need to run the stored procedure at user time intervals.

Now I am taking the following steps to solve the problem.

• Created a task to execute a stored procedure and scheduled for every 1 min.
• Inside the stored procedure, I will check the current time (min) and the scheduled interval (s).
• If it is consistent, then part of the tsql code inside the stored procedure will be executed, another wise to skip the process.

This works fine, but the stored procedure will be executed every minute (I hope someone ran into the same problem).

Looking for the best solution to solve this problem.

Answer 1

First of all, you need a little stored procedure to create 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 

Usage example:

 DECLARE @ScheduleId int; EXEC spCreateSchedule_Interval @scheduleName = 'UserA_Schedule', @intervalType = 'minutes', @interval = 27, @ScheduleId = @ScheduleId OUT; 

This should create a schedule to run every 27 minutes.

You may also need proc to create a schedule for a 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 

Usage example:

 DECLARE @ScheduleId INT; EXEC spCreateSchedule_ExactTime @scheduleName = 'UserB_Schedule', @timeToRun = '14:58:00', @ScheduleId = @ScheduleId OUT; 

This should create a schedule to run every day at 14:58.

The above two procedures can be easily combined into one. Separated here for clarity and ease of maintenance. They can also be further extended, you can parameterize @freq_type, @freq_interval, etc. All you need is documentation: https://msdn.microsoft.com/pl-pl/library/ms366342(v=sql.110).aspx

Another step is 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 use:

 EXEC spUpdateSchedule_Interval @scheduleName = 'UserB_Schedule', @intervalType = 'minutes', @interval = 25; GO 

Now you can create spUpdateSchedule_ExactTime by analogy.

The last thing you need is 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 use:

 USE msdb GO EXEC spDeleteSchedule 'UserA_Schedule'; 

Or you can easily write an alternative that will use sched_id instead of sched_name (sp_delete_schedule can get any of them).

ATTENTION: When updating and deleting procedures, you can use names or identifiers to define schedules. Although the names are more friendly to people and I used them to make the examples easier to follow, I strongly recommend that you use identifiers instead. Names should not be unique, therefore, if you manage to create two schedules with the same names, then both deleting and updating procs will fail if you do not use sched_id as a parameter.

Answer 2

Assuming this is not a frequent case, execute sp_update_schedule when the table is updated. Add this to the update procedure or as a trigger if you directly refresh the table.

Answer 3

1 Create a sql job and create step 1 (to execute sp) https://msdn.microsoft.com/en-in/library/ms190268.aspx#Anchor_2

2. Add multiple job schedules as required (using sp_add_jobschedule). Details: https://msdn.microsoft.com/en-us/library/ms366342.aspx .

Answer 4

It’s good that dispatcher time is controlled by the application.

But in the real world, why will the user constantly update the time scheduler? I want to say frequency.

Therefore, I think that whenever the time changes from the application, run this new stored procedure that will update the scheduler time using sp_update_schedule .

There is no reason to execute a stored procedure every minute. It will work only when the scheduler is changed using the application.

Answer 5

I'm not sure how your application or user code works, but you can run the SQL agent trigger from your user code to run the task by calling https://msdn.microsoft.com/nl-nl/library/ms186757.aspx . The only limitation is that the user must be the owner of the job or a member of sysadmin, see the link for more details.