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?