Publishing what I learn

SharePoint and other geekoid tidbits

Monthly Archives: March 2014

Scheduling a manual SQL Server job

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