sql - 使用 sysschedules 类似事件表 SQL Server

Question

我正在尝试开发一个定期事件日历。看起来 msdb 数据库中的 sysschedules 表可以很好地完成这项工作。

但是我面临着一些挑战，希望有人能提供帮助。

a) active_start_date:这是事件第一次发生的日期吗？或者事件开始前的任何日期？

b) 如何找到特定日期的匹配事件(一次、每日、每周、每月等)？

编辑:我没有直接使用sysschedules表。而是创建一个具有相同列的新表。

Answer 1

首先，您应该引用 sysschedules 。但据我了解，有一个错字。这个

active_start_time Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

应理解为

active_start_time Time on any day between active_start_time and active_end_time that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

考虑一下:

active_start_date Date on which execution of a job can begin. The date is formatted as YYYYMMDD. NULL indicates today's date.

这意味着，如果您将定期作业配置为每天每小时启动一次，并设置 active_start_time = 100000 和 active_start_date = 20120323，则作业直到 2012 年 3 月 23 日才会运行 10是。
但是，如果您要创建一次性运行的任务，则此字段包含确切的开始日期和时间。

对于寻找匹配事件，我的看法如下。当您更新某些重要内容(例如计划类型或开始时间)或循环计划引发下一个作业执行时，SQL 代理会计算计划的下一次运行时间并将其存储在 sysjobschedules.next_run_date 中。

因此，它总是有一个下次执行的列表，并且不能解决“查找特定日期的匹配事件”的问题。我认为你应该以同样的方式实现你的系统。

但是如果您坚持以这种方式进行操作，我们可以考虑使用 T-SQL 查询。

更新
下面的脚本可以帮助您完成任务。目前它适用于两种类型的时间表:
1.启动一次
2.每天开始，每N天一次，当天一次
据我从你的评论中了解到，你会使用它们。您可以在需要时立即以相同的方式添加其他类型(UNION ALL ... UNION ALL ...)。

函数 msdb_time_convert 将 HHMMSS 整数转换为 TIME 数据类型。

CREATE FUNCTION msdb_time_convert (
    @int_time INT
)
RETURNS TIME(0)
AS 
BEGIN
    IF NOT (@int_time BETWEEN 0 AND 235959) 
        RETURN NULL

    DECLARE @str VARCHAR(32) = CAST(@int_time AS VARCHAR(32))
    SELECT @str = REPLICATE('0', 6 - LEN(@str)) + @str
    SELECT @str = STUFF(@str, 3, 0, ':')
    SELECT @str = STUFF(@str, 6, 0, ':')

    RETURN CONVERT(TIME(0), @str, 108)
END
GO

您可以在 @find_date 变量中指定任何日期时间，以查找不同时间点的下一次运行。

DECLARE @find_date DATETIME = GETDATE()

;WITH CTE AS (
    SELECT 
        CONVERT(DATE, CAST(active_start_date AS VARCHAR(32)), 112) AS active_start_date, 
        CONVERT(DATE, CAST(active_end_date AS VARCHAR(32)), 112) AS active_end_date, 
        dbo.msdb_time_convert(active_start_time) AS active_start_time,
        dbo.msdb_time_convert(active_end_time) AS active_end_time,  
        schedule_id,
        schedule_uid,
        name,
        enabled,
        freq_type,
        freq_interval,
        freq_subday_type,
        freq_subday_interval,
        freq_relative_interval,
        freq_recurrence_factor,
        CAST(@find_date AS DATE) AS today_date,
        CAST(@find_date AS TIME(0)) AS today_time,
        DATEADD(day, DATEDIFF(day, CONVERT(DATETIME, CAST(active_start_date AS VARCHAR(32)), 112) - 1, @find_date) % NULLIF(freq_interval, 0), CAST(@find_date AS DATE)) AS next_daily_day
    FROM dbo.sysschedules
)
SELECT 
    schedule_id,
    name,
    CAST(active_start_date AS DATETIME) + active_start_time AS next_run_datetime
FROM CTE
WHERE
    enabled = 1
    AND freq_type = 1       -- 1 = One time only
    AND CAST(active_start_date AS DATETIME) + active_start_time >= @find_date

UNION ALL

SELECT 
    schedule_id,
    name,
    DATEADD(
        DAY,
        CASE WHEN CAST(next_daily_day AS DATETIME) + active_start_time > @find_date THEN 0 ELSE freq_interval END,
        CAST(next_daily_day AS DATETIME) + active_start_time 
    ) AS next_run_datetime
FROM CTE 
WHERE
    enabled = 1
    AND freq_type = 4                   -- 4 = Daily (Every freq_interval days)
    AND freq_subday_type = 1            -- 1 = At the specified time
    AND CAST(active_end_date AS DATETIME) + active_end_time >= @find_date