SQL JOB SCHEDULING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL JOB SCHEDULING

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-03-25 : 06:24:58

I have SQL 2000 SP3
I would like to run a number of JOBs via the SQL Server Agent from one Master JOB.

When i use the following e.g statement;

USE msdb
EXEC sp_start_job @job_name = 'My_Job1'

USE msdb
EXEC sp_start_job @job_name = 'My_Job2'

As you might expect all my JOBs start at once - however i'd like the jobs to start only after the preceding JOB has completed. Does anyone know an easy way to achieve this?
(Hope the T-SQL is the correct forum for this question)
Any ideas will be most appreciated.
Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-26 : 22:18:36

Put them in separate steps in main job.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-27 : 08:35:44

also gives you the option of putting in some failure/success logic

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-03-31 : 06:03:15

Thanks for your replies;
Even when you place the EXEC sp_start_job in a separate step it will still continue to start all the JOBS in one swoop - without waiting for the job to complete with success.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-31 : 23:24:40

What I meant is put all those jobs as separate step in single job, not call sp_start_job.

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-13 : 11:18:39

Sorry I haven’t picked up on this thread for a while – it’s a long story...

I have tried putting the Jobs into separate steps which still results in all Jobs starting one after other (the setup doesn’t recognise each job takes x mins to complete).
(As some of the individual Jobs I wish to schedule have up to 50 internal steps I want to keep them separate)
Is it possible to have one master Job that waits for My_Job1 to complete before starting My_Job2 etc…?

Any advice will be much appreciated,
Thanks again,
Dan

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-13 : 12:29:41

How about creating a status table and writing to it when each job is finished? You would also have to check the table at the beginning step of each job. Set the step to fail if it's not clear to start yet and retry X times for X minutes.

An infinite universe is the ultimate cartesian product.

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-15 : 10:56:33

Ive written the code below to build a log table but as im no programmer can you advise on how to write the code to change the table status.
------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE xJobLog
(JobName nvarchar(50),
DateUpdated smalldatetime,
StatusFlag char(1) )

GO
SET ANSI_PADDING OFF
--Status N = Job not yet run
Insert into xJobLog
select 'Job1', { fn NOW() }, 'N' UNION all
select 'Job2', { fn NOW() }, 'N' UNION all
select 'Job3' , { fn NOW() } , 'N' UNION all
select 'Job4' , { fn NOW() }, 'N' UNION all
select 'Job5' , { fn NOW() }, 'N' UNION all
select 'Job6' , { fn NOW() }, 'N' UNION all
select 'Job7' , { fn NOW() } , 'N'
-----------------------------------------------

In then meantime i will keep trying myself.
Thanks
Dan

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-15 : 11:43:00

dont know if this helps anyone...
I have got around the problem by placing a delay on the job - WAITFOR DELAY (neat function)
not ideal but is a way of delaying the job...Thanks

-----------------------------------

BEGIN
--wait 1 min
WAITFOR DELAY '00:01';
USE msdb
EXEC sp_start_job @job_name = 'JOB1'
END;
Go

BEGIN
--wait 2 min
WAITFOR DELAY '00:02';
USE msdb
EXEC sp_start_job @job_name = 'JOB2'
END;
Go

BEGIN
--wait 6 min
WAITFOR DELAY '00:06';
Use msdb
EXEC sp_start_job @job_name = 'JOB3'
END;
Go

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 12:39:47

You still don't get it

In a Job you let SQL Server manage it....unless they are bat files, then you will never know unless you write and interrogate a log from that step

But until you tell us what's in the job, we can't help

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 10:16:05

Try this in a query window


CREATE TABLE #xJobLog
(JobName nvarchar(50),
DateStarted smalldatetime null,
DateFinished smalldatetime null 
)

GO
SET ANSI_PADDING OFF
--Status N = Job not yet run
Insert into #xJobLog
select 'Job1', { fn NOW() }, null UNION all
select 'Job2', { fn NOW() }, null UNION all
select 'Job3' , { fn NOW() } , null UNION all
select 'Job4' , { fn NOW() }, null UNION all
select 'Job5' , { fn NOW() }, null UNION all
select 'Job6' , { fn NOW() }, null UNION all
select 'Job7' , { fn NOW() } , null


select * from #xJoblog
-----------------------------------------------



if (select count(*) from #xJoblog where jobname = 'Job1' and DateStarted = (select max(datestarted) from #xJoblog where Jobname = 'Job1' ) and DateFinished is null) = 1
	raiserror ('Job1 is not finished yet', 16, 1)

drop table #xJoblog

Use something like this as a first step in the job that is dependent on Job1 finishing. Set the step to retry X number of times with X minute intervals.

This isn't exactly how I'd do it, but hopefully it will give you an idea on how to solve your problem.

An infinite universe is the ultimate cartesian product.

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-27 : 05:05:35

Thanks Cat_Jesus, have adopted your methodology to resolve the problem.
….its a lot more practical than the WAITFOR DELAY function :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources