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?
posted @ Tuesday, August 12, 2008 11:11 AM