223 lines
9.3 KiB
Transact-SQL
223 lines
9.3 KiB
Transact-SQL
USE [msdb];
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.usp_CreateWeeklySchedules
|
|
@Env VARCHAR(10) = 'DEVL' -- Added parameter with default value
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@JobName SYSNAME,
|
|
@FrequencyPattern NVARCHAR(MAX),
|
|
@StartTime VARCHAR(50),
|
|
@ScheduleName SYSNAME,
|
|
@StartTimeInt INT,
|
|
@FreqInterval INT,
|
|
@ActiveDate INT,
|
|
@computedFreq INT,
|
|
@dbName SYSNAME; -- Added variable for database name
|
|
|
|
-- Determine database name based on environment
|
|
SET @dbName = CASE @Env
|
|
WHEN 'DEVL' THEN 'DevlDUTASJobSchedule'
|
|
WHEN 'TEST' THEN 'TestDUTASJobSchedule'
|
|
WHEN 'PROD' THEN 'ProdDUTASJobSchedule'
|
|
END;
|
|
|
|
SET @ActiveDate = CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112));
|
|
|
|
--------------------------------------------------------------------------
|
|
-- Dynamic SQL for cursor to fetch active weekly jobs from appropriate database
|
|
--------------------------------------------------------------------------
|
|
DECLARE @Sql NVARCHAR(MAX);
|
|
SET @Sql = N'
|
|
DECLARE job_cursor CURSOR FAST_FORWARD FOR
|
|
SELECT
|
|
JobName,
|
|
FrequencyPattern,
|
|
ScheduledStartTime
|
|
FROM ' + QUOTENAME(@dbName) + N'.dbo.JobControl
|
|
WHERE UPPER(Frequency) = ''WEEKLY''
|
|
AND IsActive = 1;';
|
|
|
|
-- Execute dynamic SQL to declare cursor
|
|
EXEC sp_executesql @Sql;
|
|
|
|
OPEN job_cursor;
|
|
FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
BEGIN TRY
|
|
------------------------------------------------------------------
|
|
-- Reset per-job variables
|
|
------------------------------------------------------------------
|
|
SET @FreqInterval = 0;
|
|
SET @computedFreq = 0;
|
|
SET @StartTimeInt = NULL;
|
|
SET @ScheduleName = NULL;
|
|
|
|
------------------------------------------------------------------
|
|
-- Correctly format the start time (HHMMSS without extra zeros)
|
|
------------------------------------------------------------------
|
|
SET @StartTime = LTRIM(RTRIM(ISNULL(@StartTime, '00:00:00')));
|
|
|
|
-- Parse time and convert to HHMMSS integer
|
|
DECLARE @TimeParts TABLE (part NVARCHAR(10), idx INT);
|
|
INSERT INTO @TimeParts (part, idx)
|
|
SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
|
|
FROM STRING_SPLIT(LEFT(@StartTime, 8), ':');
|
|
|
|
DECLARE @Hours INT, @Minutes INT, @Seconds INT;
|
|
|
|
SELECT @Hours = CAST(part AS INT) FROM @TimeParts WHERE idx = 0;
|
|
SELECT @Minutes = CAST(part AS INT) FROM @TimeParts WHERE idx = 1;
|
|
SELECT @Seconds = CAST(part AS INT) FROM @TimeParts WHERE idx = 2;
|
|
|
|
-- Ensure valid time ranges
|
|
SET @Hours = ISNULL(@Hours, 0);
|
|
SET @Minutes = ISNULL(@Minutes, 0);
|
|
SET @Seconds = ISNULL(@Seconds, 0);
|
|
|
|
IF @Hours < 0 OR @Hours > 23 SET @Hours = 0;
|
|
IF @Minutes < 0 OR @Minutes > 59 SET @Minutes = 0;
|
|
IF @Seconds < 0 OR @Seconds > 59 SET @Seconds = 0;
|
|
|
|
SET @StartTimeInt = (@Hours * 10000) + (@Minutes * 100) + @Seconds;
|
|
|
|
SET @ScheduleName = CONCAT(@JobName, '_WeeklySchedule');
|
|
|
|
------------------------------------------------------------------
|
|
-- Parse JSON days using a local temporary table
|
|
------------------------------------------------------------------
|
|
CREATE TABLE #Days (DayName NVARCHAR(10));
|
|
|
|
INSERT INTO #Days (DayName)
|
|
SELECT TRIM(value)
|
|
FROM OPENJSON(ISNULL(@FrequencyPattern, '{}'), '$.Days');
|
|
|
|
------------------------------------------------------------------
|
|
-- Compute bitmask correctly (values should be 1-127)
|
|
------------------------------------------------------------------
|
|
SELECT @computedFreq = ISNULL(SUM(
|
|
CASE UPPER(DayName)
|
|
WHEN 'SUN' THEN 1
|
|
WHEN 'MON' THEN 2
|
|
WHEN 'TUE' THEN 4
|
|
WHEN 'WED' THEN 8
|
|
WHEN 'THU' THEN 16
|
|
WHEN 'FRI' THEN 32
|
|
WHEN 'SAT' THEN 64
|
|
ELSE 0
|
|
END
|
|
), 0)
|
|
FROM #Days;
|
|
|
|
DROP TABLE #Days; -- Explicitly drop the temp table
|
|
|
|
SET @FreqInterval = ISNULL(@computedFreq, 0);
|
|
|
|
------------------------------------------------------------------
|
|
-- Validate freq_interval (must be 1..127)
|
|
------------------------------------------------------------------
|
|
IF @FreqInterval < 1 OR @FreqInterval > 127
|
|
BEGIN
|
|
PRINT 'Warning: Weekly job ' + @JobName +
|
|
' has invalid freq_interval (' + ISNULL(CAST(@FreqInterval AS VARCHAR(10)), 'NULL') +
|
|
'). Days found: ' + ISNULL(@FrequencyPattern, 'None') + '. Skipping.';
|
|
FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;
|
|
CONTINUE;
|
|
END
|
|
|
|
------------------------------------------------------------------
|
|
-- Debug output to verify calculations
|
|
------------------------------------------------------------------
|
|
PRINT 'Job: ' + @JobName +
|
|
', Days: ' + @FrequencyPattern +
|
|
', FreqInterval: ' + CAST(@FreqInterval AS VARCHAR(3)) +
|
|
', StartTime: ' + CAST(@StartTimeInt AS VARCHAR(6));
|
|
|
|
------------------------------------------------------------------
|
|
-- Safely detach and delete any existing schedule with this name
|
|
-- Use TRY/CATCH for schedule operations since non-existence is expected
|
|
------------------------------------------------------------------
|
|
BEGIN TRY
|
|
DECLARE @schedule_id INT;
|
|
DECLARE @attach_count INT;
|
|
|
|
-- Check if schedule exists and get its ID
|
|
SELECT @schedule_id = s.schedule_id
|
|
FROM msdb.dbo.sysschedules s
|
|
JOIN msdb.dbo.sysjobschedules js ON s.schedule_id = js.schedule_id
|
|
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
|
|
WHERE j.name = @JobName AND s.name = @ScheduleName;
|
|
|
|
IF @schedule_id IS NOT NULL
|
|
BEGIN
|
|
-- Detach from this job (if attached)
|
|
EXEC msdb.dbo.sp_detach_schedule
|
|
@job_name = @JobName,
|
|
@schedule_name = @ScheduleName;
|
|
|
|
-- Check if schedule still attached to other jobs
|
|
SELECT @attach_count = COUNT(*)
|
|
FROM msdb.dbo.sysjobschedules js
|
|
WHERE js.schedule_id = @schedule_id;
|
|
|
|
IF @attach_count = 0
|
|
BEGIN
|
|
EXEC msdb.dbo.sp_delete_schedule
|
|
@schedule_name = @ScheduleName;
|
|
PRINT 'Existing schedule deleted: ' + @ScheduleName;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'Notice: schedule ' + @ScheduleName + ' could not be deleted because it is still attached to other jobs.';
|
|
END
|
|
END
|
|
END TRY
|
|
BEGIN CATCH
|
|
-- Schedule operations might fail if schedule doesn't exist, which is fine
|
|
PRINT 'Notice: ' + ERROR_MESSAGE() + ' - proceeding with new schedule creation.';
|
|
END CATCH
|
|
|
|
------------------------------------------------------------------
|
|
-- Create the unified weekly schedule with subday config
|
|
------------------------------------------------------------------
|
|
EXEC msdb.dbo.sp_add_schedule
|
|
@schedule_name = @ScheduleName,
|
|
@enabled = 1,
|
|
@freq_type = 8, -- Weekly
|
|
@freq_interval = @FreqInterval, -- Bitmask of weekdays
|
|
@freq_recurrence_factor = 1, -- Every week
|
|
@freq_subday_type = 1, -- Once per day
|
|
@freq_subday_interval = 1,
|
|
@active_start_time = @StartTimeInt,
|
|
@active_start_date = @ActiveDate;
|
|
|
|
------------------------------------------------------------------
|
|
-- Attach the schedule to the job
|
|
------------------------------------------------------------------
|
|
EXEC msdb.dbo.sp_attach_schedule
|
|
@job_name = @JobName,
|
|
@schedule_name = @ScheduleName;
|
|
|
|
PRINT 'Weekly schedule created for job: ' + @JobName +
|
|
' (Days=' + CAST(@FreqInterval AS VARCHAR(10)) +
|
|
', Time=' + RIGHT('000000' + CAST(@StartTimeInt AS VARCHAR(6)), 6) + ')';
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
PRINT 'Error scheduling job ' + ISNULL(@JobName, 'Unknown') + ': ' + ERROR_MESSAGE();
|
|
END CATCH;
|
|
|
|
FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;
|
|
END
|
|
|
|
CLOSE job_cursor;
|
|
DEALLOCATE job_cursor;
|
|
|
|
PRINT 'All weekly job schedules processed for ' + @dbName;
|
|
END;
|
|
GO |