0

I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL.

I know how to schedule a job daily at a specific time:

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' 

On this Microsoft guide I see I can use @freq_type = 16 but I don't know how to set it and an example would be much appreciated

2

To schedule a job to run once a month, in this case midnight on the first day:

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
| improve this answer | |
  • it says The specified @job_name ('Get Date') does not exist. – Francesco Mantovani Mar 22 '18 at 8:37
  • 1
    All I posted was the code to set the schedule (sp_add_jobschedule)...do you need full code? Plus, change 'Get Date' to your job name – Kevin3NF Mar 22 '18 at 11:26
  • You teach me something today @Kevin3NF. Last question, at what level should I insert SELECT * FROM AdventureWorks2012.dbo.AWBuildVersion; which is in fact the query I want to run every month. thank you – Francesco Mantovani Mar 23 '18 at 0:18
  • 1
    In the job step part: @command=N'SELECT * FROM AdventureWorks2012.dbo.AWBuildVersion;' – Kevin3NF Mar 23 '18 at 3:38
1

In your question, you reference sp_procoption. The documentation for that command states (highlighting mine):

Sets or clears a stored procedure for automatic execution. A stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.

This has nothing to do with the other link you posted which references sp_add_schedule which states (highlighting mine):

Creates a schedule that can be used by any number of jobs.


While you could modify your existing code to put the waitfor inside the while loop and check for the day to be 1 (first day of the month), your procedure only executes when the SQL Server instance starts up. If you don't restart your SQL Server instance often, this approach will be problematic and unreliable.

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

If you want a relatively reliable way to run your stored procedure to run monthly, you either need to create a SQL Server Agent job with a monthly schedule or you could use Windows Task Scheduler and have it run monthly.

| improve this answer | |
  • I modified the first row to DECLARE @delayTime NVARCHAR(10) SET @delayTime = '23:00' but it just keeps executing in loop without stop – Francesco Mantovani Mar 22 '18 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.