Schedule SQL Job in a user configured time interva

Question

In my application (ASP.NET, C#), i need to run a stored procedure in a set of pre defined time interval(s) everyday. So that i created a sql job and scheduled the same. But the problem is, there is a option to create/modify this time intervals using the application and this will store the modified time intervals in a table. So i need to run the stored procedure in the user configured time intervals.

Now i am doing the following steps to resolve the issue.

1. Created a job to execute the stored procedure and scheduled for every 1 min.
2. Inside the stored procedure i will check the current time (min) and the scheduled interval(s).
3. If it is matched then the tsql code part inside the stored procedure will execute, other wise skip the process.

This is working fine, but the stored procedure will execute every minute (Hope somebody faced the same issue).

Looking for a better solution to solve this issue .

Answer 1

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

Answer 2

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

2 . Add multiple schedules to the job as per requirement (using sp_add_jobschedule) . details : https://msdn.microsoft.com/en-us/library/ms366342.aspx.

Answer 3

Assuming this isn't a frequent event, execute sp_update_schedule when the table is updated. Add this to the update procedure or as a trigger if directly updating the table.

Answer 4

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.

Answer 5

It is good that scheduler time is being manage by application.

But in real world, why user will keep updating scheduler time? I mean to say the frequency.

So I think whenever time is modified from application then fire this new stored procedure which will update the scheduler time using sp_update_schedule.

There is no reason for the stored procedure to execute every minute. It will only fire when scheduler is modified via application.