Publishing what I learn

SharePoint and other geekoid tidbits

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] 
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

 @SCHED = js.schedule_id 
 msdb.dbo.sysjobschedules js 
 msdb.dbo.sysschedules s 
 ON js.schedule_id = s.schedule_id 
 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 


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: