SQLServerCentral / Discuss Content Posted by Peter He / Article Discussions / Article Discussions by Author / SQL Server Job Scheduling / Latest Posts

RE: SQL Server Job Scheduling

kas07 — Thu, 27 Sep 2012 07:18:05 GMT

i have set up a job to run every five minutes.If somehow the job takes more than five minutes in execution, will the sql scheduler run another instance of same job on five minutes cycle? when the last execution has not been completed?

RE: SQL Server Job Scheduling

ruben 99661 — Wed, 01 Feb 2012 10:48:46 GMT

hi , i c your msg, and i have the same question. Can I perform this schedule jobs dynamically, with some conditional parameters ... ?? .. if you know something about it, please contact me ...

RE: SQL Server Job Scheduling

vilas979 — Fri, 26 Nov 2010 06:50:35 GMT

Thanks for such good article

RE: SQL Server Job Scheduling

Hem-586205 — Thu, 14 Feb 2008 02:05:53 GMT

Excellent article on Jobs.

RE: SQL Server Job Scheduling

Fox.David — Mon, 10 Dec 2007 10:49:11 GMT

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

RE: SQL Server Job Scheduling

Wayne West — Fri, 16 Nov 2007 15:53:07 GMT

[quote][b]Rasool Omidakhsh (11/16/2007)[/b][hr]...I have to run this dm_job everyday. ... [dw_job] I have to run this job on every second thursday. ...[/quote]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:

RE: SQL Server Job Scheduling

Larry Aue — Fri, 16 Nov 2007 14:42:05 GMT

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.. ;)

RE: SQL Server Job Scheduling

Calvin E. Wrightsman — Fri, 16 Nov 2007 14:13:12 GMT

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).

RE: SQL Server Job Scheduling

Jim McCoy-259251 — Fri, 16 Nov 2007 13:30:35 GMT

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 myDateDim oddEvenmyDate = datediff("ww","8/10/2007",date())oddEven = (myDate Mod 2) if oddEven = 0 then Main = DTSTaskExecResult_Success else Main = DTSTaskExecResult_failure end ifEnd Function

RE: SQL Server Job Scheduling

Rasool Omidakhsh — Fri, 16 Nov 2007 13:11:24 GMT

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.

RE: SQL Server Job Scheduling

Philippe Cand — Fri, 16 Nov 2007 12:14:04 GMT

[quote][b]Rasool Omidakhsh (11/16/2007)[/b][hr]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?[/quote]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

RE: SQL Server Job Scheduling

Jim McCoy-259251 — Fri, 16 Nov 2007 09:43:54 GMT

Yep, that's my kludge code all right

RE: SQL Server Job Scheduling

Rasool Omidakhsh — Fri, 16 Nov 2007 09:24:14 GMT

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?

RE: SQL Server Job Scheduling

Larry Aue — Fri, 16 Nov 2007 09:10:29 GMT

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.

RE: SQL Server Job Scheduling

Jim McCoy-259251 — Fri, 16 Nov 2007 06:32:28 GMT

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.

RE: SQL Server Job Scheduling

MattieNH — Fri, 16 Nov 2007 06:07:55 GMT

When I clicked on the [b]this script [/b]hyperlink (for running a job more often than every minute), I got the following message:[i]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 [/i]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

RE: SQL Server Job Scheduling

Greg Charles — Wed, 08 Mar 2006 08:54:00 GMT

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

RE: SQL Server Job Scheduling

MattieNH — Wed, 08 Mar 2006 08:26:00 GMT

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

SQL Server Job Scheduling

peterhe — Fri, 10 Feb 2006 14:34:00 GMT

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