Ежедневное планирование заданий SQL в настраиваемых пользователем временных интервалах

В моем приложении (ASP.NET, C#), мне нужно ежедневно запускать хранимую процедуру в наборе предварительно определенных временных интервалов. Так что я создал задание sql и запланировал то же самое. Но проблема в том, что есть возможность создавать/изменять эти временные интервалы с помощью приложения, и это сохранит измененные временные интервалы в таблице. Поэтому мне нужно запустить хранимую процедуру в настроенные пользователем интервалы времени.

Теперь я делаю следующие шаги, чтобы решить вопрос.

  1. создано задание для выполнения хранимой процедуры и запланировано для каждые 1 минуты.
  2. внутри хранимой процедуры я проверю текущее время (мин) и запланированный интервал(интервалы).
  3. если он соответствует, то часть кода tsql внутри хранимой процедуры выполнит, другие мудрые пропускают процесс.

Это работает нормально, но хранимая процедура будет выполняться каждую минуту (надеюсь, кто-то столкнулся с той же проблемой).

В поисках лучшего решения чтобы решить эту проблему .

5 6
sql-server tsql sql-server-2008 sql-job

5 ответов:

Первое, что требуется, - это небольшая хранимая процедура для создания интервальных расписаний.

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

Пример использования: 

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

Это должно создать расписание для запуска каждые 27 минут.

Вам также может понадобиться proc для создания расписания на определенное время: 

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

Пример использования: 

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

Это должно создать расписание для запуска каждый день в 14: 58.

Вышеприведенные две процедуры можно было бы легко объединить в одну. Разделены здесь для ясности и простоты изложения. поддержка. Они также могут быть дополнительно расширены, вы можете параметризовать @freq_type, @freq_interval и т. д. Все, что вам нужно, есть в документации: https://msdn.microsoft.com/pl-pl/library/ms366342 (v=sql.110).aspx

Еще одним шагом являются процедуры обновления существующих расписаний: 

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

И использование: 

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

Теперь вы можете создать spUpdateSchedule_ExactTime по аналогии.

Последнее, что вам нужно-хранимая процедура для удаления расписание: 

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

И его использование: 

USE msdb
GO 

EXEC spDeleteSchedule 'UserA_Schedule';

Или вы можете легко написать альтернативу, которая будет использовать schedule_id вместо schedule_name (sp_delete_schedule может получить любой из них).

Примечание: В процедурах обновления и удаления можно использовать имена или идентификаторы для идентификации расписаний. В то время как имена более дружелюбны к человеку, и я использовал их для примеров, чтобы было легче следовать, я настоятельно рекомендую вам использовать ID вместо этого. Имена не обязаны быть уникальными, таким образом, если вы создадите два расписания с одинаковым именем, то оба процесса удаления и обновления завершатся неудачей, если вы не используете schedule_id в качестве параметра.

2

Предполагая, что это не частое событие, выполните sp_update_schedule при обновлении таблицы. Добавьте это в процедуру обновления или в качестве триггера при непосредственном обновлении таблицы.

4

1 Создайте задание sql и выполните шаг 1 (чтобы выполнить sp) https://msdn.microsoft.com/en-in/library/ms190268.aspx#Anchor_2

2 . Добавьте несколько расписаний к заданию в соответствии с требованиями (используя sp_add_jobschedule). подробности: https://msdn.microsoft.com/en-us/library/ms366342.aspx .

3

Хорошо, что время планировщика управляется приложением.

Но в реальном мире, почему пользователь будет продолжать обновлять время планировщика? Я имею в виду частоту.

Поэтому я думаю, что всякий раз, когда время изменяется из приложения, затем запустите эту новую хранимую процедуру, которая обновит время планировщика с помощью sp_update_schedule.

Нет никаких причин для выполнения хранимой процедуры каждую минуту. Он будет срабатывать только при изменении планировщика через приложение.

3

Я не уверен, как работает ваше приложение или пользовательский код, но вы можете запустить триггер агента SQL из вашего пользовательского кода, чтобы запустить задание, вызвав https://msdn.microsoft.com/nl-nl/library/ms186757.aspx единственным ограничением является то, что пользователь должен быть владельцем задания или членом sysadmin, смотрите ссылку для получения более подробной информации.

2