sql - 如何使用 sp_add_job 无限期地每 10 秒运行一次作业？

Question

如何使用 sp_add_job 安排作业无限期地每 10 秒运行一次？我不确定我是否在 sp_add_jobschedule 上使用了正确的参数。

下面是我正在使用的 T-SQL 代码。

BEGIN TRY
BEGIN TRAN

DECLARE @jobId BINARY(16)

--Add job
EXEC msdb.dbo.sp_add_job @job_name=N'Update TimeStamp in table', @job_id = @jobId OUTPUT

--Add step to job
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Do SQL Stuff', 
        @step_id=1, 
        @subsystem=N'TSQL', 
        @command=N'Exec StoredProc', 
        @database_name=N'DB', 
        @flags=0

--Add schedule to job
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every minute', 
        @freq_type=4, --
        @freq_interval=64, 
        @freq_subday_type=0x2, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0,
        @active_start_date=20150403, 
        @active_end_date=99991231

COMMIT TRAN
END TRY

BEGIN CATCH
    SELECT ERROR_Message(), ERROR_Line();
    ROLLBACK TRAN
END CATCH

Answer 1

这个。测试了一下，可以用

USE [msdb];
GO

EXEC dbo.sp_add_job  
    @job_name = N'job name',
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa',
    @notify_level_eventlog=0 ;
GO 

EXEC sp_add_jobstep  
    @job_name = N'job name',  
    @step_id = 1, 
    @step_name = N'step name',  
    @subsystem = N'TSQL',  
    @command = N'

-- actual query
insert into dbo.Test1 (number, string, some_date)
values (rand(), ''text'', GETDATE())

    ',   
    @retry_attempts = 0,  
    @retry_interval = 0,
    @on_success_action = 1,
    @database_name=N'OddsAndEnds' ;
GO  

EXEC dbo.sp_add_jobserver  
    @job_name = N'job name',
    @server_name = N'(local)' ;
GO 

EXEC sp_add_jobschedule 
    @job_name = N'job name',
    @name = N'test job schedule', 
    @enabled = 1,   --enabled
    @freq_type = 4, -- on daily basis
    @freq_interval = 1, -- don't use this one
    @freq_subday_type = 2,  -- units between each exec: seconds
    @freq_subday_interval = 10,  -- number of units between each exec
    @active_start_date=20190312, 
    @active_end_date=99991231, 
    @schedule_uid=N'8912aa53-ffe9-4f31-b6cb-9a8e2f1ee6e3'