question

apple avatar image
apple asked

SQL Agent Scheduling to run on specific days.

Hello! I need to schedule a job to run on the 1st of month ,31st and Monday(excluding the near Mondays for first of month and end of month). So for November 2015 it will be executed on the 1st,9 th,16 th,23rd and 30. Appreciate any suggestions Thanks
sql-agentschedule
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
Yay! A SQL Agent schedule question. :) So now I guess you know I love the scheduler. It's so incredibly flexible, and a big reason for that is the support for one job running on multiple schedules. Having said all of that, I'm still not sure how to handle the exclusion you mentioned without checking in a job step. 1. Schedule 1: Monthly on first day of every month. 2. Schedule 2: Monthly on last day of every month. 3. Schedule 3: Weekly on every Monday. The combination of those 3 schedules gets you everything except your exclusion.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
Funny. I don't do complex scheduling in SQL Agent, so approached the question from the angle of an exception within the job step like @Tom Staab mentioned. This would be the first step of a job that was scheduled every day (my original thought), or according to the schedule in Tom's answer, which is somewhat more elegant: if (select datepart(dd, getdate())) = 1 begin raiserror('First of the month; running job.', 0, 0) with nowait end else begin if ((select datepart(dd, getdate())) = 30 AND (select datepart(dd, getdate() + 1)) = 1) OR ((select datepart(dd, getdate())) = 31 AND (select datepart(dd, getdate() + 1)) = 1) begin raiserror('End of the month; running job.', 0, 0) with nowait end else begin if (select datename(weekday, getdate())) = 'Monday' AND (select datepart(dd, getdate())) between 8 and 23 begin raiserror('Monday; running job.', 0, 0) with nowait end else begin declare @msg varchar(1000) = 'Stopping job. '; select @msg = @msg + datename(weekday, getdate()) + ', ' + convert(varchar, datepart(dd, getdate())) + ' ' + DATENAME(month, getdate()) + ', is not a scheduled date.' raiserror(@msg, 11, 1) with nowait end end end Just set the job step to end the job with success on failure and to go to the next stop on success.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Tom and Ken I will try it .
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.