SQL job schedule question

Question

SQL job schedule question

caojunhe24

Ten Centuries

Points: 1334
More actions
April 3, 2017 at 7:49 am

#320072

Hi experts,
Now I want to schedule some store procs using SQL job agent with following sequence.
eg. 1)sp1
2)sp2
3)sp3
but the thing is these sp should only run every month on the FIRST BUSINESS day.
Do you have any idea how can i handle this situation using SQL job agent?
Thanks
Ausitn

Viewing 11 posts - 1 through 10 (of 10 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

What do you defined as a Business day? First weekday, or do you want to exclude public holidays (if so, are you storing details of public holidays?)? I simply the first weekday, there is already an option for this in the schedules. Select Monthly for the frequency, then the The radio button, First in the drop down, and weekday in the next drop down.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Step 1 = schedule logic
Step 2 = sp1
Step 3 = sp2
Step 4 = sp3

Presumably where you are the first business day of the month is always going to be a Monday, Tuesday or Wednesday? If so, create three schedules, one for the first Monday of the month, one for the first Tuesday and one for the first Wednesday. In Step 1 you'll need to define the working day logic, probably with the help of a calendar table. If it's a business day and Step 2 hasn't already run this month, go to Step 2.

John

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 3

ho Thom,

yes when i said business day means except holidays.

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 4

hi john ,

you may misunderstood my point, i was saying i want to run all the sps(sp1 &sp2&sp3) on first bussiness day.
and here the business day not always start from monday-we except holiday here.

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 5

Thom,

iI already have a holiday table.
e.g holiday date
newyear 1-1-2017
.. ..

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

No, I understood it perfectly. That's why the logic in Step 1 needs to test whether today (ie the day on which the job runs) is a business day. It also needs to test whether the job has already run this month.

John

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 7

hi,

the first businessday it can be any weekday . eg 8-1-2017 is tuesday

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Yes, my apologies - of course it can. But the advice is still similar - you just need five schedules, for the first Monday of the month, the first Tuesday and so on. If today is Friday and it also happens to be the first business day of the month, go to Step 2. Come to think of it, you don't actually need to check whether the job has previously run this month if you get your calendar logic right.

John

Thom A SSC Guru Points: 99133 More actions · Answer 9

So, for your first step, you could do something like this:
DECLARE @FirstWD DATE;

SELECT TOP 1 @FirstWD = [Date] FROM DimDate DD WHERE DD.[Calendar Year] = DATEPART(YEAR, GETDATE()) AND DD.[Calendar Month] = DATEPART(MONTH, GETDATE()) AND DD.[Working Day] = 1 ORDER BY DD.[Date];

IF @FirstWD != CAST(GETDATE() AS date) BEGIN

RAISERROR('Not First Working day; Aborting job', 16, 1);

END
Then set your success to go to step 2, and your failure to quit the job, reporting success.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 10

Thanks Thom &John,

You guys idea is valid. thanks a lot now i can able to slove the problem.

Again Thanks a lot!

Austin