Post reply

SQL job schedule question

  • 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

  • April 3, 2017 at 8:03 am

    #1936291

    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

  • April 3, 2017 at 8:04 am

    #1936292

    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

  • April 3, 2017 at 8:07 am

    #1936293

    ho Thom,

    yes when i said business day means except holidays.

  • April 3, 2017 at 8:09 am

    #1936295

    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.

  • April 3, 2017 at 8:13 am

    #1936297

    Thom,

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

  • April 3, 2017 at 8:13 am

    #1936298

    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

  • April 3, 2017 at 8:16 am

    #1936299

    hi,

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

  • April 3, 2017 at 8:21 am

    #1936301

    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

  • April 3, 2017 at 9:01 am

    #1936313

    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

  • April 3, 2017 at 12:19 pm

    #1936382

    Thanks Thom &John,

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

    Again Thanks a lot!

    Austin

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

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