CODEWITHSUNDEEP
sqlt-sqlsql-job
user2748147
user2748147

Reputation: 59

How to schedule a job using sp_add_job to run every 10 seconds indefinitely?

How do I schedule a job using sp_add_job to run every 10 seconds indefinitely? I am not sure if I am using the right parameters on the sp_add_jobschedule.

Below is the T-SQL code I am using. 

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

Upvotes: 3

Views: 3002

Answers (2)

osynavets
osynavets

Reputation: 1530

This one. Tested it, it's working

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'

Upvotes: 1

Francesco Sgaramella
Francesco Sgaramella

Reputation: 1017

Check the documentation here. It discourages to run a job every 10 seconds:

freq_subday_intervalis int, with a default of 0. Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1.

Upvotes: 0

Related Questions