hobby:development:sql:database_scheduler

Differenze

Queste sono le differenze tra la revisione selezionata e la versione attuale della pagina.

Link a questa pagina di confronto

Entrambe le parti precedenti la revisione Revisione precedente
Prossima revisione		 Revisione precedente
hobby:development:sql:database_scheduler [2026/08/14 10:33] – mauro.cortesehobby:development:sql:database_scheduler [2026/08/14 14:00] (versione attuale) – mauro.cortese
Linea 4: Linea 4:
 \\ \\
 \\ \\
-<sxh sql> 
-/* ============================================================================ 
-   TABLE-DRIVEN SCHEDULER FOR SQL SERVER 
-   ---------------------------------------------------------------------------- 
-   Creates: 
-     - dbo.SchedulerConfig        (job definitions: what to run, when, how) 
-     - dbo.SchedulerConfigTimes   (fixed daily run times, for ScheduleType = 'DAILY_TIME') 
-     - dbo.SchedulerLog           (execution history) 
-     - dbo.sp_SchedulerDispatcher (main orchestrator, no CURSOR object used) 
-     - supporting nonclustered indexes for the dispatcher's filter conditions 
  
-   Intended usage: create a single SQL Server Agent Job with one step +Scheduler table-driven in SQL Server, basato su SQL Server Agent con una tabella di configurazione e una stored procedure "dispatcher".
-       EXEC dbo.sp_SchedulerDispatcher; +
-   scheduled to run every minute. The actual per-job frequency is fully +
-   controlled by the data in SchedulerConfig / SchedulerConfigTimes. +
-   ============================================================================ */+
  
-SET NOCOUNT ON; +=== Architettura === 
-GO+  - **Tabella di configurazione:** definisce quali procedure eseguire, con che frequenza, se sono attive 
 +  - **Tabella di log:** traccia le esecuzioni (successo/errore, durata) 
 +  - **Stored procedure dispatcher:** legge la config, decide cosa è "dovuto" eseguire, lancia le procedure con EXEC dinamico e gestione errori 
 +  - **Un solo SQL Server Agent Job:** che gira ogni minuto (o ogni X minuti) e chiama il dispatcher 
 +Vantaggio: non serve creare un job Agent per ogni procedura, ne basta uno solo che orchestra tutto in base alla tabella.
  
-/* ---------------------------------------------------------------------------- 
-   1) CONFIGURATION TABLE 
-   Stores the definition of every job. ScheduleType drives which of the other 
-   scheduling columns/tables are actually used for that row. 
----------------------------------------------------------------------------- */ 
-IF OBJECT_ID('dbo.SchedulerConfig', 'U') IS NULL 
-BEGIN 
- CREATE TABLE dbo.SchedulerConfig 
- ( 
- ID INT IDENTITY(1,1) PRIMARY KEY, 
- JobName NVARCHAR(100) NOT NULL, -- Human-friendly name, not used by the engine 
- ProcedureSchema SYSNAME NOT NULL DEFAULT 'dbo', -- Schema of the target procedure 
- ProcedureName SYSNAME NOT NULL, -- Name of the stored procedure to execute 
- Parameters NVARCHAR(MAX) NULL, -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC''' 
  
- -- 'INTERVAL'   -> runs every FrequencyMinutes minutes +=== Creazione delle tabelle === 
- -- 'DAILY_TIME' -> runs at specific times of day, listed in SchedulerConfigTimes +<sxh sql> 
- -- 'ONE_TIME'   -> runs once at SpecificDateTime, then never again +-- ----------------------------------------------------------------------------- 
- ScheduleType VARCHAR(15) NOT NULL, +-- 1) CONFIGURATION TABLE 
- +--    Stores the definition of every job. ScheduleType drives which of the other 
- FrequencyMinutes INT NULL, -- required only when ScheduleType = 'INTERVAL' +--    scheduling columns/tables are actually used for that row. 
- SpecificDateTime DATETIME NULL, -- required only when ScheduleType = 'ONE_TIME' +-- ----------------------------------------------------------------------------- 
- +CREATE TABLE dbo.scheduler_config( 
- StartTime TIME NULL, -- optional time-of-day window, applies to INTERVAL only +    IdJob               INT IDENTITY(1,1) PRIMARY KEY, 
- EndTime TIME NULL, +    JobName             NVARCHAR(100)     NOT NULL,                -- Human-friendly name, not used by the engine 
- WeekDays VARCHAR(20) NULL, -- optional allowed weekdays (1=Monday..7=Sunday), applies to INTERVAL and DAILY_TIME +    ProcedureSchema     SYSNAME           NOT NULL DEFAULT 'dbo',  -- Schema of the target procedure 
- +    ProcedureName       SYSNAME           NOT NULL,                -- Name of the stored procedure to execute 
- IsActive BIT NOT NULL DEFAULT 1, -- enable/disable without deleting the row +    Parameters          NVARCHAR(MAX)     NULL,                    -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC''' 
- IsRunning BIT NOT NULL DEFAULT 0, -- prevents overlapping executions of the same job +    ScheduleType        VARCHAR(15)       NOT NULL,                -- INTERVAL   -> runs every FrequencyMinutes minutes 
- LastRunDate DATETIME NULL, -- timestamp of the last time this job started +                                                                   -- DAILY_TIME -> runs at specific times of day, listed in scheduler_config_times 
- +                                                                   -- ONE_TIME   -> runs once at SpecificDateTime, then never again 
- CONSTRAINT CK_SchedulerConfig_ScheduleType +    FrequencyMinutes    INT               NULL,                    -- required only when ScheduleType = INTERVAL 
- CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME')) +    SpecificDateTime    DATETIME          NULL,                    -- required only when ScheduleType = ONE_TIME 
- ); +    StartTime           TIME              NULL,                    -- optional time-of-day window, applies to INTERVAL only 
-END+    EndTime             TIME              NULL,                    --         
 +    WeekDays            VARCHAR(20)       NULL,                    -- optional allowed weekdays (1=Monday..7=Sunday), applies to INTERVAL and DAILY_TIME 
 +    IsActive            BIT               NOT NULL DEFAULT 1,      -- enable/disable without deleting the row 
 +    IsRunning           BIT               NOT NULL DEFAULT 0,      -- prevents overlapping executions of the same job 
 +    LastRunDate         DATETIME          NULL,                    -- timestamp of the last time this job started 
 +       
 +    CONSTRAINT sK_Schedu_cerConfig_ScheduleType 
 +        CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME')) 
 +    );
 GO GO
- +  
-/* ---------------------------------------------------------------------------- +-- ----------------------------------------------------------------------------------- 
-   2) FIXED DAILY RUN TIMES +-- 2) FIXED DAILY RUN TIMES 
-   Holds one or more fixed times of day for jobs whose ScheduleType = 'DAILY_TIME'. +--    Holds one or more fixed times of day for jobs whose ScheduleType = 'DAILY_TIME'. 
-   Example: a job can run at both 08:00 and 18:00 by inserting two rows here. +--    Example: a job can run at both 08:00 and 18:00 by inserting two rows here. 
----------------------------------------------------------------------------- */ +-- ----------------------------------------------------------------------------------- 
-IF OBJECT_ID('dbo.SchedulerConfigTimes', 'U') IS NULL +CREATE TABLE dbo.scheduler_config_times ( 
-BEGIN +    Id             INT IDENTITY(1,1) PRIMARY KEY, 
- CREATE TABLE dbo.SchedulerConfigTimes +    ConfigID       INT               NOT NULL REFERENCES dbo.scheduler_config(IdJob), 
- ( +    RunTime        TIME              NOT NULL,        -- e.g. '08:00:00' 
- ID INT IDENTITY(1,1) PRIMARY KEY, +    LastRunDate    DATE              NULL             -- last calendar date this specific slot fired; prevents re-firing within the same day 
- ConfigID INT NOT NULL REFERENCES dbo.SchedulerConfig(ID), +    );
- RunTime TIME NOT NULL, -- e.g. '08:00:00' +
- LastRunDate DATE NULL -- last calendar date this specific slot fired; prevents re-firing within the same day +
- ); +
-END+
 GO GO
- +  
-/* ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- 
-   3) EXECUTION LOG +-- 3) EXECUTION LOG 
-   Keeps an execution history for every run of every job, including duration +--    Keeps an execution history for every run of every job, including duration 
-   and any error raised. +--    and any error raised. 
----------------------------------------------------------------------------- */ +-- ---------------------------------------------------------------------------- 
-IF OBJECT_ID('dbo.SchedulerLog', 'U') IS NULL +CREATE TABLE dbo.scheduler_log ( 
-BEGIN +    ID              INT IDENTITY(1,1) PRIMARY KEY, 
- CREATE TABLE dbo.SchedulerLog +    ConfigID        INT               NOT NULL,   -- FK back to scheduler_config.ID 
- ( +    ProcedureName   SYSNAME           NOT NULL,   -- denormalized for quick reading without a join 
- ID INT IDENTITY(1,1) PRIMARY KEY, +    StartDate       DATETIME          NOT NULL, 
- ConfigID INT NOT NULL, -- FK back to SchedulerConfig.ID +    EndDate         DATETIME          NULL, 
- ProcedureName SYSNAME NOT NULL, -- denormalized for quick reading without a join +    Outcome         VARCHAR(20)       NULL,       -- 'RUNNING' / 'OK' / 'ERROR' 
- StartDate DATETIME NOT NULL, +    ErrorMessage    NVARCHAR(MAX)     NULL        -- populated only when Outcome = 'ERROR' 
- EndDate DATETIME NULL, +    );
- Outcome VARCHAR(20) NULL, -- 'RUNNING' / 'OK' / 'ERROR' +
- ErrorMessage NVARCHAR(MAX) NULL -- populated only when Outcome = 'ERROR' +
- ); +
-END+
 GO GO
- +  
-/* ---------------------------------------------------------------------------- +-- ----------------------------------------------------------------------------- 
-   4) SUPPORTING INDEXES +-- 4) SUPPORTING INDEXES 
-   The dispatcher filters on ScheduleType / IsActive / IsRunning on every run +--    The dispatcher filters on ScheduleType / IsActive / IsRunning on every run 
-   (every minute), so these columns need a covering index to avoid a table +--    (every minute), so these columns need a covering index to avoid a table 
-   scan as SchedulerConfig grows. +--    scan as scheduler_config grows. 
----------------------------------------------------------------------------- */ +-- ----------------------------------------------------------------------------- 
-IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SchedulerConfig_Dispatch' AND object_id = OBJECT_ID('dbo.SchedulerConfig')) +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfig_Dispatch 
-BEGIN +ON dbo.scheduler_config (ScheduleType, IsActive, IsRunning) 
- CREATE NONCLUSTERED INDEX IX_SchedulerConfig_Dispatch +INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime);
- ON dbo.SchedulerConfig (ScheduleType, IsActive, IsRunning) +
- INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime); +
-END+
 GO GO
- +   
-IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SchedulerConfigTimes_ConfigID' AND object_id = OBJECT_ID('dbo.SchedulerConfigTimes')) +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfigTimes_ConfigID 
-BEGIN +ON dbo.scheduler_config_times (ConfigID) 
- CREATE NONCLUSTERED INDEX IX_SchedulerConfigTimes_ConfigID +INCLUDE (RunTime, LastRunDate);
- ON dbo.SchedulerConfigTimes (ConfigID) +
- INCLUDE (RunTime, LastRunDate); +
-END+
 GO GO
  
-/* ---------------------------------------------------------------------------- +</sxh>
-   5) DISPATCHER PROCEDURE +
-   Main orchestrator, meant to be called every minute by a single SQL Server +
-   Agent Job. Handles INTERVAL, DAILY_TIME and ONE_TIME scheduling. +
-   No CURSOR object is used: due jobs are collected in a table variable and +
-   walked with a WHILE loop, which is required only because each dynamic +
-   EXEC needs its own isolated TRY/CATCH so one failing job never blocks +
-   the others. All status/log updates that don't need per-row isolation +
-   are done as set-based statements instead. +
----------------------------------------------------------------------------- */ +
-CREATE OR ALTER PROCEDURE dbo.sp_SchedulerDispatcher +
-AS +
-BEGIN +
- SET NOCOUNT ON;+
  
- DECLARE @Now DATETIME = GETDATE(); +=== Dispatcher procedure ===
- DECLARE @Today DATE = CAST(@Now AS DATE); +
- DECLARE @NowTime TIME = CAST(@Now AS TIME); +
- DECLARE @TodayWeekDay VARCHAR(1) = CAST(DATEPART(WEEKDAY, @Now) AS VARCHAR(1));+
  
- -- Working table with a row number, used to walk through the due jobs one +<sxh sql>
- -- at a time without allocating a CURSOR object. LogID is filled later via +
- -- an OUTPUT clause, avoiding a lookup query inside the loop. +
- DECLARE @DueJobs TABLE +
- ( +
- RowNum INT IDENTITY(1,1) PRIMARY KEY, +
- ConfigID INT, +
- ProcedureSchema SYSNAME, +
- ProcedureName SYSNAME, +
- Parameters NVARCHAR(MAX), +
- TimeSlotID INT NULL, +
- LogID INT NULL +
- ); +
- +
- -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate, +
- --    and (if set) we're inside the allowed time window and weekday list. +
- INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID) +
- SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL +
- FROM dbo.SchedulerConfig +
- WHERE IsActive = 1 +
- AND IsRunning = 0 +
- AND ScheduleType = 'INTERVAL' +
- AND (LastRunDate IS NULL OR DATEDIFF(MINUTE, LastRunDate, @Now) >= FrequencyMinutes) +
- AND (StartTime IS NULL OR @NowTime >= StartTime) +
- AND (EndTime IS NULL OR @NowTime <= EndTime) +
- AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%'); +
- +
- -- 2) DAILY_TIME jobs: due when current time has reached a configured +
- --    RunTime slot and that slot hasn't already fired today. +
- INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID) +
- SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID +
- FROM dbo.SchedulerConfig c +
- INNER JOIN dbo.SchedulerConfigTimes t ON t.ConfigID = c.ID +
- WHERE c.IsActive = 1 +
- AND c.IsRunning = 0 +
- AND c.ScheduleType = 'DAILY_TIME' +
- AND (c.WeekDays IS NULL OR c.WeekDays LIKE '%' + @TodayWeekDay + '%') +
- AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today) +
- AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108); +
- +
- -- 3) ONE_TIME jobs: due once, when current datetime has reached +
- --    SpecificDateTime and the job has never run before. +
- INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID) +
- SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL +
- FROM dbo.SchedulerConfig +
- WHERE IsActive = 1 +
- AND IsRunning = 0 +
- AND ScheduleType = 'ONE_TIME' +
- AND LastRunDate IS NULL +
- AND @Now >= SpecificDateTime; +
- +
- -- Nothing due: exit early, no further statements needed. +
- IF NOT EXISTS (SELECT 1 FROM @DueJobs) +
- RETURN; +
- +
- -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE, +
- -- instead of doing it row-by-row inside the loop. +
- UPDATE c +
- SET c.IsRunning = 1, c.LastRunDate = GETDATE() +
- FROM dbo.SchedulerConfig c +
- INNER JOIN @DueJobs d ON d.ConfigID = c.ID; +
- +
- -- Same for DAILY_TIME slots: stamp all fired slots at once. +
- UPDATE t +
- SET t.LastRunDate = @Today +
- FROM dbo.SchedulerConfigTimes t +
- INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID +
- WHERE d.TimeSlotID IS NOT NULL; +
- +
- -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated +
- -- IDs directly via OUTPUT so the loop below needs no lookup query. +
- DECLARE @LogMap TABLE (ConfigID INT, LogID INT); +
- +
- INSERT INTO dbo.SchedulerLog (ConfigID, ProcedureName, StartDate, Outcome) +
- OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID) +
- SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' +
- FROM @DueJobs; +
- +
- UPDATE d +
- SET d.LogID = m.LogID +
- FROM @DueJobs d +
- INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID; +
- +
- -- Execute each due procedure individually: dynamic SQL with per-job error +
- -- handling genuinely requires row-by-row processing, so this is a plain +
- -- WHILE loop keyed on RowNum rather than a CURSOR. +
- DECLARE @i INT = 1, @Count INT = (SELECT COUNT(*) FROM @DueJobs); +
- DECLARE @ConfigID INT, @Schema SYSNAME, @Name SYSNAME, @Params NVARCHAR(MAX), @LogID INT; +
- DECLARE @SQL NVARCHAR(MAX); +
- +
- WHILE @i <= @Count +
- BEGIN +
- SELECT +
- @ConfigID = ConfigID, +
- @Schema = ProcedureSchema, +
- @Name = ProcedureName, +
- @Params = Parameters, +
- @LogID = LogID +
- FROM @DueJobs +
- WHERE RowNum = @i; +
- +
- -- QUOTENAME() protects schema/procedure names against injection and +
- -- reserved-word issues; parameters, if present, are appended as-is. +
- SET @SQL = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name) +
- + CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END; +
- +
- BEGIN TRY +
- EXEC (@SQL); +
- +
- UPDATE dbo.SchedulerLog SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID; +
- END TRY +
- BEGIN CATCH +
- -- One failing procedure never stops the loop: error is logged, +
- -- next job proceeds. +
- UPDATE dbo.SchedulerLog +
- SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE() +
- WHERE ID = @LogID; +
- END CATCH +
- +
- -- Always release the running flag, whether the procedure succeeded or failed. +
- UPDATE dbo.SchedulerConfig SET IsRunning = 0 WHERE ID = @ConfigID; +
- +
- SET @i += 1; +
- END +
-END +
-GO+
  
-/* ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- 
-   6) SAMPLE CONFIGURATION ROWS (optional — comment out or delete if not needed) +-- DISPATCHER PROCEDURE 
----------------------------------------------------------------------------- */ +-- Main orchestrator, meant to be called every minute by a single SQL Server 
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType, FrequencyMinutes, StartTime, EndTime, WeekDays)+-- Agent Job. Handles INTERVAL, DAILY_TIME and ONE_TIME scheduling. 
 +-- No CURSOR object is used: due jobs are collected in a table variable and 
 +-- walked with a WHILE loop, which is required only because each dynamic 
 +-- EXEC needs its own isolated TRY/CATCH so one failing job never blocks 
 +-- the others. All status/log updates that don't need per-row isolation 
 +-- are done as set-based statements instead. 
 +-- -------------------------------------------------------------------------- 
 +-- SAMPLE CONFIGURATION ROWS 
 +-- -------------------------------------------------------------------------- */ 
 +-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, FrequencyMinutes, StartTime, EndTime, WeekDays)
 -- VALUES ('Import CSV X3', 'sp_ImportCsvX3', 'INTERVAL', 15, '06:00', '22:00', '1,2,3,4,5'); -- VALUES ('Import CSV X3', 'sp_ImportCsvX3', 'INTERVAL', 15, '06:00', '22:00', '1,2,3,4,5');
 + 
 -- DECLARE @NewConfigID INT; -- DECLARE @NewConfigID INT;
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType)+-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType)
 -- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME'); -- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME');
 -- SET @NewConfigID = SCOPE_IDENTITY(); -- SET @NewConfigID = SCOPE_IDENTITY();
--- INSERT INTO dbo.SchedulerConfigTimes (ConfigID, RunTime)+-- INSERT INTO dbo.scheduler_config_times (ConfigID, RunTime)
 -- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00'); -- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00');
- +  
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType, SpecificDateTime)+-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, SpecificDateTime)
 -- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00'); -- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00');
 +-- ----------------------------------------------------------------------------
 +CREATE OR ALTER PROCEDURE dbo.sp_scheduler_dispatcher
 +AS
 +BEGIN
 +    SET NOCOUNT ON;
 + 
 +    DECLARE @Now DATETIME            = GETDATE();
 +    DECLARE @Today DATE              = CAST(@Now AS DATE);
 +    DECLARE @NowTime TIME            = CAST(@Now AS TIME);
 +    DECLARE @TodayWeekDay VARCHAR(1) = CAST(DATEPART(WEEKDAY, @Now) AS VARCHAR(1));
 + 
 +    -- Working table with a row number, used to walk through the due jobs one
 +    -- at a time without allocating a CURSOR object. LogID is filled later via
 +    -- an OUTPUT clause, avoiding a lookup query inside the loop.
 +    DECLARE @DueJobs TABLE (
 +        RowNum           INT IDENTITY(1,1) PRIMARY KEY,
 +        ConfigID         INT,
 +        ProcedureSchema  SYSNAME,
 +        ProcedureName    SYSNAME,
 +        Parameters       NVARCHAR(MAX),
 +        TimeSlotID       INT NULL,
 +        LogID            INT NULL
 +    );
 + 
 +    -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate,
 +    --    and (if set) we're inside the allowed time window and weekday list.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
 +        FROM dbo.scheduler_config
 +        WHERE IsActive = 1
 +            AND IsRunning = 0
 +            AND ScheduleType = 'INTERVAL'
 +            AND (LastRunDate IS NULL OR DATEDIFF(MINUTE, LastRunDate, @Now) >= FrequencyMinutes)
 +            AND (StartTime IS NULL OR @NowTime >= StartTime)
 +            AND (EndTime IS NULL OR @NowTime <= EndTime)
 +            AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%');
 + 
 +    -- 2) DAILY_TIME jobs: due when current time has reached a configured
 +    --    RunTime slot and that slot hasn't already fired today.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID
 +        FROM dbo.scheduler_config c
 +        INNER JOIN dbo.scheduler_config_times t ON t.ConfigID = c.ID
 +        WHERE c.IsActive = 1
 +            AND c.IsRunning = 0
 +            AND c.ScheduleType = 'DAILY_TIME'
 +            AND (c.WeekDays IS NULL OR c.WeekDays LIKE '%' + @TodayWeekDay + '%')
 +            AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today)
 +            AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108);
 + 
 +    -- 3) ONE_TIME jobs: due once, when current datetime has reached
 +    --    SpecificDateTime and the job has never run before.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
 +        FROM dbo.scheduler_config
 +        WHERE IsActive = 1
 +            AND IsRunning = 0
 +            AND ScheduleType = 'ONE_TIME'
 +            AND LastRunDate IS NULL
 +            AND @Now >= SpecificDateTime;
 + 
 +    -- Nothing due: exit early, no further statements needed.
 +    IF NOT EXISTS (SELECT * FROM @DueJobs)
 +        RETURN;
 + 
 +    -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE,
 +    -- instead of doing it row-by-row inside the loop.
 +    UPDATE c SET
 +         c.IsRunning = 1
 +        ,c.LastRunDate = GETDATE()
 +    FROM dbo.scheduler_config c
 +        INNER JOIN @DueJobs d ON d.ConfigID = c.ID;
 + 
 +    -- Same for DAILY_TIME slots: stamp all fired slots at once.
 +    UPDATE t SET
 +        t.LastRunDate = @Today
 +    FROM dbo.scheduler_config_times t
 +        INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID
 +    WHERE d.TimeSlotID IS NOT NULL;
 + 
 +    -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated
 +    -- IDs directly via OUTPUT so the loop below needs no lookup query.
 +    DECLARE @LogMap TABLE (
 +         ConfigID INT
 +        ,LogID INT
 +        );
 + 
 +    INSERT INTO dbo.scheduler_log (ConfigID, ProcedureName, StartDate, Outcome)
 +         OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID)
 +         SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' FROM @DueJobs;
 + 
 +    UPDATE d SET
 +        d.LogID = m.LogID
 +    FROM @DueJobs d
 +        INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID;
 + 
 +    -- Execute each due procedure individually: dynamic SQL with per-job error
 +    -- handling genuinely requires row-by-row processing, so this is a plain
 +    -- WHILE loop keyed on RowNum rather than a CURSOR.
 +    DECLARE @i INT = 1;
 +    DECLARE @Count INT = (SELECT COUNT(*) FROM @DueJobs);
 +    DECLARE @ConfigID INT;
 +    DECLARE @Schema SYSNAME;
 +    DECLARE @Name SYSNAME;
 +    DECLARE @Params NVARCHAR(MAX)
 +    DECLARE @LogID INT;
 +    DECLARE @SqlCmd NVARCHAR(MAX);
 + 
 +    WHILE @i <= @Count
 +    BEGIN
 +        SELECT
 +            @ConfigID = ConfigID,
 +            @Schema   = ProcedureSchema,
 +            @Name     = ProcedureName,
 +            @Params   = Parameters,
 +            @LogID    = LogID
 +        FROM @DueJobs
 +            WHERE RowNum = @i;
 + 
 +        -- QUOTENAME() protects schema/procedure names against injection and
 +        -- reserved-word issues; parameters, if present, are appended as-is.
 +        SET @SqlCmd = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name) + CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END;
 + 
 +        BEGIN TRY
 +            EXEC (@SqlCmd);
 +            UPDATE dbo.scheduler_log SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID;
 +        END TRY
 +        BEGIN CATCH
 +            -- One failing procedure never stops the loop: error is logged,
 +            -- next job proceeds.
 +            UPDATE dbo.scheduler_log
 +            SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE()
 +            WHERE ID = @LogID;
 +        END CATCH
 + 
 +        -- Always release the running flag, whether the procedure succeeded or failed.
 +        UPDATE dbo.scheduler_config SET IsRunning = 0 WHERE ID = @ConfigID;
 + 
 +        SET @i += 1;
 +    END
 +END
 +GO
 </sxh> </sxh>
  • hobby/development/sql/database_scheduler.1786696428.txt.gz
  • Ultima modifica: 2026/08/14 10:33
  • da mauro.cortese