February 10, 2006 at 2:34 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/phe/sqlserverjobscheduling.asp
March 8, 2006 at 8:26 am
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
March 8, 2006 at 8:54 am
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
November 16, 2007 at 6:07 am
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
November 16, 2007 at 6:32 am
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.
November 16, 2007 at 9:10 am
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.
November 16, 2007 at 9:24 am
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?
November 16, 2007 at 9:43 am
Yep, that's my kludge code all right
November 16, 2007 at 12:14 pm
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
November 16, 2007 at 1:11 pm
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.
November 16, 2007 at 1:30 pm
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
November 16, 2007 at 2:13 pm
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).
November 16, 2007 at 2:42 pm
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.. 😉
November 16, 2007 at 3:53 pm
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]
December 10, 2007 at 10:49 am
Were you able to complete the conditional scheduling for a SQL report?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply