SQL Server Job Scheduling

  • peterhe

    SSChampion

    Points: 11362

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/phe/sqlserverjobscheduling.asp

  • MattieNH

    SSCertifiable

    Points: 7463

    Very interesting article.  Is the only way to create multiple schedules for the same job through code, or can you do it through the Enterprise Manager interface?

    Mattie

     

  • Greg Charles

    SSC-Forever

    Points: 45403

    You can add multiple schedules for a job in Enterprise Manager.  Open the the job properties, click on the Schedules tab, and click on the New Schedule button.  As you define each schedule, save it by clicking OK.  Be sure that all the schedules are enabled.

    Greg

    Greg

  • MattieNH

    SSCertifiable

    Points: 7463

    When I clicked on the this script hyperlink (for running a job more often than every minute), I got the following message:

    Articles with tags Administering, sqlserverjobscheduling, 2288, Job_30sec.sql

    Search for any content tagged Administering & sqlserverjobscheduling & 2288 & Job_30sec.sql

    Sorry, nothing found for this search

    Not that putting it in one of those multi-line text boxes would have helped; they don't have vertical scroll bars in my browser.

    Mattie

  • Jim McCoy-259251

    Mr or Mrs. 500

    Points: 569

    Very interesting. I have had my share of scheduling challenges like running a job every second Thursday and still haven't come up with a good non-kludgy way of running a job based on receipt of a file to process which would arrive at a pre-determined time ( which may or may not arrive at exactly that time) but the output of the process is also very time dependant.

  • Larry Aue

    Ten Centuries

    Points: 1027

    Start it to run when you expect it and have it run every minute until it finds the file. Sure, it runs often, but ends up doing very little. When it finds the file, you can reschedule the job for the next scheduled time to look for the file as the last step.

  • Rasool Omidakhsh

    SSC-Addicted

    Points: 449

    Ok we can add multiple schedule to a job. But can we do conditional schedule? Can we for example have a schedule for everyday except the last Thursday of the month? In general how do you handel conditions we may have in SQL Agent?

  • Jim McCoy-259251

    Mr or Mrs. 500

    Points: 569

    Yep, that's my kludge code all right

  • jhasbufuidh

    SSC Eights!

    Points: 992

    Rasool Omidakhsh (11/16/2007)


    Ok we can add multiple schedule to a job. But can we do conditional schedule? Can we for example have a schedule for everyday except the last Thursday of the month? In general how do you handle conditions we may have in SQL Agent?

    Not sure if there is anyway to do this in SQL Agent however what you can do is to use a job step that runs a ssis package where you have a script that will either fail or succeed the package based on any condition you want. Then you handle the job step success or failure to either exit the job reporting success or branch to some or next step.

    Philippe

    BI Guy

  • Rasool Omidakhsh

    SSC-Addicted

    Points: 449

    Thanks Philippe. However the question was if we can handel condition for schedule. For example lets say I have a Job that runs 4 SSIS packages that loads my data mart (I call it dm_Job). I have to run this dm_job everyday. I have another job that runs 20 SSIS packages that loads the whole data warehose (I call it dw_job). I have to run this job on every second thursday. I have to start both these jobs at 00:45 AM. Here is what the issue; if today is the thursday that I am loading the data warehouse I should not run the data mart job because it is part of the dw job. So I need to know if I can have conditions(exceptions) in the schedule.

  • Jim McCoy-259251

    Mr or Mrs. 500

    Points: 569

    Using DTS in 2000 I run a check for every second Thursday with the following code and set the 'job' to run every Thursday. If it is the right day the DTS continues otherwise it simply fails.

    '*******************************************************

    ' Visual Basic ActiveX Script

    '*******************************************************

    ' Test for the second week since a Job can only be set

    ' to work within a week. Prime with a run date.

    Function Main()

    Dim myDate

    Dim oddEven

    myDate = datediff("ww","8/10/2007",date())

    oddEven = (myDate Mod 2)

    if oddEven = 0 then

    Main = DTSTaskExecResult_Success

    else

    Main = DTSTaskExecResult_failure

    end if

    End Function

  • Calvin E. Wrightsman

    SSC Journeyman

    Points: 76

    One thing Peter failed to mention is that if you want to see lots of job history, be sure the job history log is big enough (Job System within SQL Server Agent properties).

  • Larry Aue

    Ten Centuries

    Points: 1027

    An easy way to do it is to have a first step that determines if today is the day the dw_job should run or the dm_job. You can cause the step to fail based on your criteria of choice (you can use a SQL step or ActiveX to do this). Then it's simply On Failure... load one, or On Succes.... load the other, in steps 2 and 3. Remember to quit on success and failure in 2, tho.. 😉

  • Wayne West

    SSC-Insane

    Points: 22586

    Rasool Omidakhsh (11/16/2007)


    ...I have to run this dm_job everyday. ... [dw_job] I have to run this job on every second thursday. ...

    I don't see a problem with this, I would do it along these lines. dw_job has one schedule, occurs monthly, 2nd Thursday, runs once at 00:45. dm_job has five schedules, first one is weekly on Friday through Wednesday, the other schedules run 1st, 3rd, 4th, and last Thursday.

    Unless I misunderstood your saying second Thursday and you meant every other Thursday, in which case do dw as Weekly, Every 2 Weeks, Starting 11/01/2007, then have dm_job doing the Thursday run Every 2 Weeks starting 11/08/2007. dm would still need the Friday-Wednesday schedule.

    I think that would do it, but I could be wrong. :hehe:

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Fox.David

    Valued Member

    Points: 73

    Were you able to complete the conditional scheduling for a SQL report?

Viewing 15 posts - 1 through 15 (of 19 total)

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