| Author |
Topic |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
 Posted - 2004-03-16 : 08:33:04
|
| Any brilliant ideas as to how to schedule a SQL Server Agent Job that is dependent on many other jobs(Probably running at the same time) completing prior to it Starting?Duane. |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-03-16 : 08:53:31
|
| Two things I can think of, which I'm sure some of the other YAK masters can better, but here goes!First: As the first step in your job that needs the others to have completed, query sysjobhistory for all the jobs you are interested in having run_status=1 in your required timeframe (daily or whatever).Or ...Second: Have each individual job write it's name to a single column table you create. Put a trigger on the Insert of the table to check for the total count - if it equals the number of jobs you need, issue an alert that you have defined to execute the job that is dependant on the other jobs. Truncate the table so the process starts all over again.Raymond |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-16 : 09:19:27
|
| Thanks Raymond,Looks like the sysjobhistory can do it for me.I've never had much need for this in the past, but now that I do this forum helped out once again!Duane. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-16 : 10:40:11
|
| Yeah. Seems like Queries from sysjobhistory with WAITFOR DELAY inside of a given amount of loops or until all jobs are completed can work as a watchdog type first step, Which will return succesfull when all jobs are complete or return failure when the loop count has been exceeded will do the trick here.Thanks againDuane. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-16 : 13:06:23
|
| If a job is dependent on other jobs, then those dependencies should be built into the primary job using job steps. You should not have multiple jobs in this situation.Tara |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-16 : 13:12:55
|
| I understand and agree with what you are saying here Tara, but the multiple jobs will probably have to run at the same time. I am not sure that it is possible to do this in the primary job using job steps though, Is it?Duane. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-16 : 13:15:55
|
| What do you mean at the same time though? If job1 kicks off at 10am, does job2 also kick off at 10am? If job1 is dependent on job2, then shouldn't it wait?Tara |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-16 : 13:19:54
|
| No, I mean Job1, Job2, Job3 and Job4 Kick off at 10am and Job5 is dependent on all of them. So Job5 must obviously wait for Jobs 1 Through 4 to complete.(I work in a Data Warehouse environment - with huge amounts of data).Duane. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-23 : 05:40:33
|
| For those of you who might be interested, I solved this problem like this:1.) First created a control table which holds information regarding the job(with multiple dependencies being run) SQLCheck - is a userdefined sqlstatement to check if all dependant jobs have completed. LoopCount - is the amount of times you would like to perform the sqlscheck before reporting the error LoopInterValHHHMMSS - is the amount of time you would like the process to sleep for between each sqlcheck.CREATE TABLE DD_MultiDependantScheduling(JobName VARCHAR(100), SQLCheck VARCHAR(4000), LoopCount INT, LoopInterValHHHMMSS VARCHAR(9));2.) Populate the Control Table. The sqlcheck should include "TRUNCATE TABLE #RES; INSERT INTO #RES VALUES(''Y'')" under the condition when all jobs have successfully completed and "TRUNCATE TABLE #RES; INSERT INTO #RES VALUES(''N'')" under the condition when not all of the jobs have successfully completed. Dont worry about creating the table #RES here as the stored proc listed further down already does that.3.) Create the stored proc as follows:CREATE PROCEDURE MultiDependantScheduling(@JobName VARCHAR(100))ASDECLARE @MaxLoopCT INTDECLARE @LoopCT INTDECLARE @SQLCheck VARCHAR(4000)DECLARE @DelayTime VARCHAR(9)DECLARE @Successfull VARCHAR(1)SET @MaxLoopCT = (SELECT LoopCount FROM DD_MultiDependantScheduling WHERE JobName = @JobName)SET @SQLCheck = (SELECT SQLCheck FROM DD_MultiDependantScheduling WHERE JobName = @JobName)SET @DelayTime = (SELECT LoopInterValHHHMMSS FROM DD_MultiDependantScheduling WHERE JobName = @JobName)CREATE TABLE #RES(Successfull VARCHAR(1))EXEC(@SQLCheck)SET @Successfull = (SELECT Successfull FROM #RES)SET @LoopCT = 1WHILE @Successfull <> 'Y' AND @LoopCT <= @MaxLoopCTBEGIN WAITFOR DELAY @DelayTime EXEC(@SQLCheck) SET @Successfull = (SELECT Successfull FROM #RES) SET @LoopCT = @LoopCT + 1ENDIF @Successfull <> 'Y'BEGIN RAISERROR('Not all of this jobs dependencies have completed yet!',16,1) END--****************************************************************************4.) In the first job dependent on the Multiple jobs(which could run at the same time) Create a Transact SQL task (which must be the first task) which calls this stored proc. If the raiseerror occurs then the job fails and execution is haulted, if not the job continues to execute.Duane. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-23 : 16:05:42
|
| Seehttp://www.nigelrivett.net/Scheduler.htmlDepends how far you want to go.A simple scheduler with dependencies and priority windows per step and alarms will take a couple of days to write. The problem is defining exactly what is needed for the business which is why I don't offer this as a product but tailor it for each client.Saying that I'm turning a limitted version into a product at the moment.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Scheduling