SharePoint and other geekoid tidbits
Monthly Archives: March 2014
21 March 2014Posted by on
When testing SQL Server jobs I often have to schedule a manual job to run. I do this by editing the job, editing the manual schedule, enabling it and setting a time. This is tedious and fiddly and I’ve found that if you schedule it too soon – less than 90 seconds away – it’s likely to not run. I’ve wasted a lot of time either waiting for jobs to complete only to find out that they haven’t started, or twiddling my thumbs waiting 2 or more minutes for a job to start.
So with a bit of help from some forum questions I’ve come up with a script that schedules a job to run in 90 seconds’ time.
USE [master] GO DECLARE @jobName sysname, @scheduleName sysname, @SCHED int, @JOB uniqueidentifier, @start_date int, @run_time int SET @jobName = 'My Job' SET @scheduleName = 'My Manual Schedule' SELECT TOP 1 @JOB = job_id FROM msdb.dbo.sysjobs WHERE [name] = @jobName SELECT @SCHED = js.schedule_id FROM msdb.dbo.sysjobschedules js JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id WHERE js.job_id = @JOB AND s.[name] = @scheduleName SELECT @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112)) SELECT @run_time = CONVERT(int, REPLACE(CONVERT(char(8), CONVERT(varchar(8), DATEADD(s, 90, GETDATE()), 108)), ':', '')) --SELECT @SCHED, @start_date, @run_time EXEC msdb.dbo.sp_update_schedule @schedule_id = @SCHED, @enabled = 1, @active_start_date = @start_date, @active_start_time = @run_time GO