Scheduling Jobs in 2005

Question

Scheduling Jobs in 2005

Mr J

Hall of Fame

Points: 3595
More actions
August 12, 2009 at 8:38 pm

#135318

Hi Friends,
I have a question i want to create a job that runs at (2am) from the beginning of each month and stops on the 2nd Friday of that month and then starts again on the 4th Monday of the month and keeps running to the end of the month.
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

What does the job do?

You could write queries (if datepart(...)) that execute depending on the day of the month. Schedule the job for every day, only run it when it hits those days.

Mr J Hall of Fame Points: 3595 More actions · Answer 2

Hi Steve

The job runs a SP. I can not see a way to run the SP within the time frames that i want using the scheduled jobs, because i can't see a way for it to start the beginning of the month and then end on the 2nd friday of the month.

Cheers

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 3

Calculate the 2nd Friday in T-SQL. It's not a hard algorithm, but you need to work through the possible range of days and check the date.

So if you can do that, which isn't that hard.

datepart( dw, getdate()) = Friday (depends on your start date)

The 2nd Friday will always fall between the 8th and 14th of the month.

So

if (datepart() = Friday) and (day > 7 and < 15)

exec myproc

That's an easy step to run for the beginning of the month. You could add more logic in that step, but I'd probably add another step that always runs. In that step, calculate if you're beyond the last Monday and then run the proc.

The scheduler would run these steps every day, but the proc would only execute some days.

Mr J Hall of Fame Points: 3595 More actions · Answer 4

cheers buddy 🙂

Thanks for the help

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 5

My pleasure. I don't like to give the whole answer until you've had a chance to try it, but if you get stuck, post what you've got and we'll help you out.

Mr J Hall of Fame Points: 3595 More actions · Answer 6

Thanks again Steve all sorted and workin a treat 😎

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!