Início / dba / Perguntas / 199965
Accepted
Francesco Mantovani
Asked: 2018-03-13 00:34:23 +0800 CST

T-SQL: Como criar um trabalho que é executado mensalmente

  • 772

Já sei como agendar trabalhos mensais usando SQL Server Agent > Jobs, agora gostaria de aprender como agendar esse trabalho usando T-SQL.

Eu sei como agendar um trabalho diariamente em um horário específico:

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'

Neste guia da Microsoft vejo que posso usar @freq_type = 16, mas não sei como configurá-lo e um exemplo seria muito apreciado

sql-server stored-procedures

2 respostas

  1. Best Answer

    Para agendar um trabalho para ser executado uma vez por mês, neste caso, à meia-noite do primeiro dia:

    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. Na sua pergunta, você faz referência a sp_procoption. A documentação para esse comando afirma (destacando a minha):

    Define ou limpa um procedimento armazenado para execução automática. Um procedimento armazenado definido para execução automática é executado sempre que uma instância do SQL Server é iniciada.

    Isso não tem nada a ver com o outro link que você postou que faz referência a sp_add_schedule que afirma (destacando o meu):

    Cria um agendamento que pode ser usado por qualquer número de trabalhos .

    Embora você possa modificar seu código existente para colocar o waitforloop while dentro do loop while e verificar se o dia é 1 (primeiro dia do mês), seu procedimento só é executado quando a instância do SQL Server é inicializada. Se você não reiniciar sua instância do SQL Server com frequência, essa abordagem será problemática e não confiável.

    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

    Se você deseja uma maneira relativamente confiável de executar seu procedimento armazenado mensalmente, você precisa criar um trabalho do SQL Server Agent com um agendamento mensal ou pode usar o Agendador de Tarefas do Windows e executá-lo mensalmente.

    • 1

relate perguntas