主页 / dba / 问题 / 199965
Accepted
Francesco Mantovani
Asked: 2018-03-13 00:34:23 +0800 CST

T-SQL：如何创建每月运行的作业

  • 772

我已经知道如何使用 来安排每月的工作SQL Server Agent > Jobs，我现在想学习如何使用 T-SQL 来安排这样的工作。

我知道如何每天在特定时间安排工作：

CREATE PROCEDURE MyTask
 AS
BEGIN  
    SET NOCOUNT ON;
    -- Now is set to run evry day at 23:00
    -- But I need to run it every 1th of the month 
    declare @delayTime nvarchar(50)
    set @delayTime = '23:00'

    while 1 = 1
    begin
        waitfor time @delayTime 
        begin
            -- Here the query I want to run every month
            select * from AdventureWorks2012.dbo.AWBuildVersion;
        end
    end
END

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
                @OptionName = 'startup',
                @OptionValue = 'on'

在此Microsoft 指南中，我看到我可以使用@freq_type = 16，但我不知道如何设置它，非常感谢您提供一个示例

sql-server stored-procedures

2 个回答

  1. Best Answer

    要安排一个作业每月运行一次，在这种情况下是第一天的午夜：

    USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get Date',
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=0, 
    @notify_level_netsend=0, 
    @notify_level_page=0, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get today''s date', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=N'Select getdate()', 
    @database_name=N'master', 
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'First day of month', 
    @enabled=1, 
    @freq_type=16, 
    @freq_interval=1, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20180312, 
    @active_end_date=99991231, 
    @active_start_time=0, 
    @active_end_time=235959, 
    @schedule_uid=N'7e73cf5c-8036-41a2-af0d-3b2151862684'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
    • 2

  2. 在您的问题中，您引用sp_procoption. 该命令的文档状态（突出显示我的）：

    设置或清除自动执行的存储过程。每次启动 SQL Server 实例时，都会运行一个设置为自动执行的存储过程。

    这与您发布的引用sp_add_schedule的其他链接无关（突出显示我的）：

    创建一个可供任意数量的作业使用的计划。

    虽然您可以修改现有代码以将其waitfor放入 while 循环并检查日期是否为 1（每月的第一天），但您的过程仅在 SQL Server 实例启动时执行。如果您不经常重新启动 SQL Server 实例，那么这种方法将有问题且不可靠。

    SET @delayTime = '23:00'

WHILE 1 = 1
BEGIN
    BEGIN
        WAITFOR TIME @delayTime

        --is this the first day of the month?
        IF datepart(day, getdate()) <> 1
        BEGIN
            CONTINUE
        END

        -- Here the query I want to run every month
        SELECT *
        FROM AdventureWorks2012.dbo.AWBuildVersion;
    END
END

    如果您想要一种相对可靠的方式来运行存储过程以每月运行一次，您需要创建一个每月计划的 SQL Server 代理作业，或者您可以使用 Windows 任务计划程序并让它每月运行一次。

    • 1

相关问题