1

I want to create a SQL agent schedule that occurs during a date range. I can't use "recurs every n weeks" because the process that it will fire off will change with each month.
I need to set the frequency to be akin to occurs monthly, 1st Monday through the 2nd Friday of every 1 month. That way I am assured to be only running the process starting with the first full week of every month

Am I crazy for thinking of a schedule like this? Shirley(sic) I can't be the only one. I have looked at sp_add_schedule for any hints, none to be found there. I have tried Googling, but I can't come up with the right query syntax thus keep coming up empty.

Perhaps if one could help me come up with the correct question, that could lead me in the right direction.

::Update1: I am going to add date logic to the script.

Improve this question

2 Answers 2

Reset to default
2

I have dome something like this before by putting logic into the code you are calling to verify if it's in that range and just schedule to run everyday and if it's in that range execute, otherwise exit out gracefully?

Improve this answer
3
  • Interesting. I'll have to check that out
    – RateControl
    Commented Jan 6, 2012 at 19:08
  • I am going to add date logic to the task. Thanks!
    – RateControl
    Commented Jan 6, 2012 at 19:55
  • Yeah, for anything more complicated than run every day or every Monday, this is usually the way to go. Just put a note in your wiki or in the scheduling agent call that says that there is "will execute first week of the month" logic in the sproc.
    – Brandon
    Commented Jan 6, 2012 at 21:18
3

How about mutiple, recurring Monthly Schedules where you have selected the first Monday of every 1 month on Schedule 1, the first Tuesday of every 1 month on Schedule 2, ... the second Friday of every 1 month? See the following pic to get the idea of what I'm thinking: enter image description here

Improve this answer
2
  • Yeah, that is what I was thinking about setting up as a last resort. I just figured someone else out there had already done this. Thanks!
    – RateControl
    Commented Jan 6, 2012 at 18:35
  • While I applaud your effort in keeping the solution within the sql agent scheduling, I am going to go a different way.
    – RateControl
    Commented Jan 6, 2012 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.