July 29, 2010 at 5:34 am
Hi,
Is it possible to create a custom job schedule for a job in management studio.
I have a job which needs to run every 5th day of the month, however if the 5th falls on the weekend I want it to run on the following Monday. The job schedule in management studio does not have any option to cater for this, so I was wondering is there a way to create a custom schedule based on T sql.
Thanks
July 29, 2010 at 12:38 pm
I would schedule the job to run every day, and if it's an 'invalid' day then exit the job, and if it's a 'valid' day, actually do the work you intend to do.
declare @ValidRunDate bit
set @ValidRunDate = 0
if( --== Is it the 5th, and a weekday? ==--
datepart(dd, getdate()) = 5 and
datename(dw, getdate()) not in ('Saturday', 'Sunday')
)
begin
set @ValidRunDate = 1
end
if( --== Was last Sunday the 5th? ==--
datepart(dd, dateadd(dd, -1, getdate())) = 5 and
datename(dw, getdate()) = 'Monday'
)
OR
( --== Was last Saturday the 5th? ==--
datepart(dd, dateadd(dd, -2, getdate())) = 5 and
datename(dw, getdate()) = 'Monday'
)
begin
set @ValidRunDate = 1
end
if @ValidRunDate = 0
begin --== Nothing to do today ==--
return
end
if @ValidRunDate = 1
begin
print 'Your code goes here'
end
- Jeff
July 30, 2010 at 2:18 am
Thanks!
How do I fire the job off if the validdate is true?
July 30, 2010 at 8:49 am
Whatever the job does, stick that code in the final Begin-End block where I have "Your Code Goes Here".
It'll only actually execute when it meets the date criteria.
Another route would be to have 2 jobs, one that runs the daily check for the valid date, and if so, runs:
exec msdb.dbo.sp_start_job @job_name = 'Your Job'
- Jeff
August 2, 2010 at 1:42 am
Top Stuff, really helpful
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply