December 2, 2011 at 10:16 am
I have a SQL Agent job that needs to run every day except the first weekday of the month. Is there a way to schedule exceptions like this within SQL Agent?
This brings up a general question: let's say I have a really odd schedule. Like, I want a job to run every hour of every weekday between 9 a.m. and 5 p.m., but not on the last weekday of the month and not on the first weekday of the month. How would you accomplish this automatically? :w00t:
December 2, 2011 at 10:21 am
What I do is add a line in the job proc (assume stored proc) that check to see if the current day is the first Weekday of the month. If so just return or else execute the procedure.
If you can't figure out how to know the first weekday of every month I can post the source for a function to do this.
The probability of survival is inversely proportional to the angle of arrival.
December 2, 2011 at 10:25 am
Now that is a clever solution, but I was hoping for a way to do this in SQL Agent. I would prefer to be able to change a job schedule without modifying anyone's code.
(People can get a little touchy about that, you know. :-))
December 2, 2011 at 10:39 am
SQL Agent front end has no way to do this that I know of. But all you need to do is add these lines of TSQL to your Job script (assuming you are just executing a stored procedure):
declare @dw1 int
set @dw1 = DATEPART(dw,convert(datetime,convert(char(2),getdate(),101) + '/01/' + convert(char(4),datepart(YEAR,getdate()))))
if @dw1 = 1 or @dw1 = 7
set @dw1 = 2
if DATEPART(dw,getdate()) <> @dw1
execute sp_YourJobScript
The probability of survival is inversely proportional to the angle of arrival.
December 2, 2011 at 10:41 am
actually you can eliminate the variable and just do this:
if DATEPART(dw,convert(datetime,convert(char(2),getdate(),101) + '/01/' + convert(char(4),datepart(YEAR,getdate())))) between 2 and 6
execute sp_yourProc
(sheesh my keyboard is terrible on this computer... but you get the genral idea... see if today id the first weekday if not run you procedure)
The probability of survival is inversely proportional to the angle of arrival.
December 2, 2011 at 10:50 am
Someone posted this idea on Twitter. It is similar to yours, but doesn't require an update to the stored procedure.
Add a first step to the job which checks for the first weekday. If true, exit the job reporting success. If false, continue on to step two.
That's a helpful idea, but it short-circuits the error reporting functions in SQL Agent. What if I improperly define the date search parameters? The job won't run and it won't report any errors.
Also, I have a report utility which shows what jobs are scheduled when. It reports against the actual schedules in the msdb database for this. If I add this exception, then the utility won't properly report the schedule.
What I really need is some kind of custom scheduling application for SQL Agent. Surely, someone else has run into this need before. Do you know of any application like this?
December 2, 2011 at 10:59 am
What I had originally suggested was to add the code I posted to your job step, no need to modify an existing procedure.
Glad you got if worked out though.:-):-)
The probability of survival is inversely proportional to the angle of arrival.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply