Can an MSSQL Job Change Its Own Active Schedule?

jobssql serversql-server-2016t-sql

I am planning a new job for MSSQL Sever 2016.

I'd like the job to run on Sunday, every 10 minutes, until it sees a confirmation record is added to a table it checks as the final step in the job. If the record is there, the job modifies its current schedule so it will not run again until next Sunday. This leads me to wonder when the schedule updates in a "natural" job cycle. I wonder if my change would be overwritten before it could be used.

So, can I use msdb.dbo.sp_update_jobschedule in a job's final step to do this to the current job and what technical pitfalls might I encounter doing that?

Examples and/or alternate solutions are always welcomed.

Best Answer

You can, using the stored procedure sp_update_schedule

    declare @shcedule_id int

    if exists (select 1 from your_table where condition = 'met') begin

    select @shcedule_id  = s.schedule_id from sysjobs j
    inner join sysjobschedules s on j.job_id = s.job_id
    where j.name = 'test_job'


    sp_update_schedule @schedule_id = 9, @freq_type = 4, @freq_recurrence_factor = 1, 
    @active_start_date = 20190529, @active_start_time = 155500


    end

This will change it to weekly, recurring every one 1 week at 3:55 pm.

you can set it back to daily by setting @freq_type = 4

here's the Microsoft doc on the SP:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017

Related Question