Job Schedules running in less than 10 seconds

by | Aug 12, 2008 | BizTalk Community Blogs via Syndication

I have needed for some SQL jobs to run every second. I went into the Jobs and started to configure the job, unfortunately, the lowest value I can choose is minute:

So I found that in the script, the underlying values for Occurs (@freq_subday_interval) every are as follow:

Value Description (unit)
1 At the specified time
2 Seconds
4 Minutes
8 Hours

So I scripted the schedule like this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EverySecond', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=4, 
        @freq_subday_type=2, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20080812, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959

I got the following error:

Msg 14278, Level 16, State 1, Procedure sp_verify_schedule, Line 214

The schedule for this job is invalid (reason: The specified ‘@freq_subday_interval’ is invalid.).

Here is the logic in the sp_verify_schedule

  IF ((@freq_subday_type <> 0x1) AND (@freq_subday_interval < 1))
     OR
     ((@freq_subday_type = 0x2) AND (@freq_subday_interval < 10))
  BEGIN
    SELECT @reason = FORMATMESSAGE(14200, '@freq_subday_interval')
    RAISERROR(14278, -1, -1, @reason)
    RETURN(1) -- Failure
  END

So there are a few things that can be done to get around it:

In the job use the following code:

EXEC stored_proc;
WAITFOR DELAY '00:00:01';
EXEC stored_proc; -- again
WAITFOR DELAY '00:00:01';
EXEC stored_proc; -- and again

Or by tracking through the various stored procedures (sp_add_jobschedule and sp_add_schedule) we see that the following table is where it ends up: msdb.dbo.sysschedules, so we can just go update the table after we scheduled the job.

      UPDATE msdb.dbo.sysschedules
        SET name              = ISNULL(@schedule_name, name),
            enabled              = ISNULL(@enabled, enabled),
         freq_type            = ISNULL(@freq_type, freq_type),
         freq_interval        = ISNULL(@freq_interval, freq_interval),
         freq_subday_type     = ISNULL(@freq_subday_type, freq_subday_type),
         freq_subday_interval = ISNULL(@freq_subday_interval, freq_subday_interval),
         freq_relative_interval  = ISNULL(@freq_relative_interval, freq_relative_interval),
         freq_recurrence_factor  = ISNULL(@freq_recurrence_factor, freq_recurrence_factor),
         active_start_date    = ISNULL(@active_start_date, active_start_date),
         active_end_date         = ISNULL(@active_end_date, active_end_date),
         active_start_time    = ISNULL(@active_start_time, active_start_time),
         active_end_time         = ISNULL(@active_end_time, active_end_time)
      WHERE schedule_uid = @schedule_uid

I decided to modify the sp_verify_schedule

  IF ((@freq_subday_type <> 0x1) AND (@freq_subday_interval < 1))
     OR
     ((@freq_subday_type = 0x2) AND (@freq_subday_interval < 1))
  BEGIN
    SELECT @reason = FORMATMESSAGE(14200, '@freq_subday_interval')
    RAISERROR(14278, -1, -1, @reason)
    RETURN(1) -- Failure
  END

Did I happen to mention the disclaimer on the right side of my blog?