if my Job is disabled... then disable these other ones.
-
i'm looking into a way where i can build a job
to check and see if other jobs are disabled.
need some help with the script though.
basically it's like this. for example; say you have
the following jobs.
JOB 1
JOB 2
JOB 3
JOB 4
i would like to build a step which checks on the status of other jobs.
if JOB 2 is disabled, then automatically disable JOB 3 & JOB 4.
if job 1 = disabled
then disable JOB 2, 3, 4
is this possible?
thoughts?
_________________________
-
you can read the job status from the job tables and use the appropriate stored procs to enable or disable them as required.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
The 2005 view is
SELECT
* FROM msdb.dbo.sysjobs_view.
You'll see the enabled column, and you can code against that value. As for the sproc that you use to disable the other jobs that is:
EXEC
msdb.dbo.sp_update_job @job_name=N'test', @enabled=0
'test' being the name of the job you gave it, not what I gave it.
Lezza
-
yes. this is the logic exactly, but how
do i initiate some thing like the following:
IF @job_name=N'test' = @enabled=0
THEN SET @job_name=N'OtherJob' @enabled=0
ELSE
END
starting & stopping the jobs is no problem,
but getting it to work based on an IF/THEN/ELSE
is where i'm getting confused.
thoughts?
_________________________
-
I'm not quite understanding what you are asking. If you create a job that runs the logic, then that's the method for doing this. You would schedule the job to run whenever you wanted it to. So, I'm wondering if you are asking about how to trigger the job off whenever this condition occurs?
Lezza
-
lets see if i can explain this better.
the logic checks a job for it's enabled state.
if i have 4 individual jobs (job1, job2, job3, job4) and
they all must run in order 1, 2, 3, 4 etc. and at any
point an SQL DBA might disable one of the Jobs. then
the jobs that follow should also be disabled.
so the logic basically checks the enabled state, and will
then disable the following jobs accordingly.
if the job is already enabled, then do nothing.
does this make sense?
and... thanks for your input by the way.
_________________________
-
You're welcome! So, I think I'm understanding just a bit more. What you're wanting is if a DBA disabled one of the four jobs, at that time, you want all the others to also be disabled?
-
thats it exactly!
thoughts?
_________________________
-
There are two ways to do this that I can see:
1) run a "prefix" job scheduled either before all the one ones or on a frequent schedule. (How frequent depends on how frequently you think that the admin might actually re-enable or re-disable any of the steps, but it's not very taxing to the system to do this, so you can run it pretty often.) This "prefix job" one checks the status of all the jobs and establishes any dependencies that should be available but aren't. Then it programmatically disables the jobs that *shouldn't* be available, but still are, if necessary.
This is the approach I would take if the job-dependency logic branched, IOW if it wasn't a simple sequence.
2) create a "master" job that includes the other jobs as steps, and only schedule the master job. Set the steps to stop on failure rather than continuing.
This is a good approach if your job sequence *is* simple/without branches, as it seems to be.
>L<
-
yeah... i did create a master job for jobs 1 - 20, and
for various reasons it just wasn't feasable.
so i decided to go with a (Safety Job) which checked and disabled
accordingly. if i can write the IF/THEN/ELSE logic i was going to
simply have this job run ever top & bottom of the hour. that should
keep things safe actually.
the sequence was actually simple. it wasn't going to check over every job,
but rather just a handful of jobs, and doesn't need logic to determine which
job was next in line. i could just create the Safety Job with a single step
per other job which needed to be disabled if the previous job was disabled.
for example:
Safety Job
Step1: IF (Job 1) is disabled THEN disable (Job 2) ELSE do nothing.
Step2: IF (Job 2) is disabled THEN disable (Job 3) ELSE do nothing.
Step3: IF (Job 3) is disabled THEN disable (Job 4) ELSE do nothing.
and this is how i was going to do it.
again it's the IF/THEN code which i have trouble with.
even if i was able to come up with it... what subsystem do i use in the
job?
thoughts?
_________________________
-
>> again it's the IF/THEN code which i have trouble with
I'm sorry, I thought you got that code earlier in the thread:
[Lezza]
>>
The 2005 view is
SELECT * FROM msdb.dbo.sysjobs_view.
You'll see the enabled column, and you can code against that value. As for the sproc that you use to disable the other jobs that is:
EXEC msdb.dbo.sp_update_job @job_name=N'test', @enabled=0
'test' being the name of the job you gave it, not what I gave it.
Lezza
<<
... are you saying this isn't working for you?
>L<
-
Right. I got this before and both work properly.
The problem is how does one script the if/then into it?
_________________________
-
>>The problem is how does one script the if/then into it?
Maybe we're talking about two different things, or maybe I'm missing something...
If I create a new job, and then I create a new Step,
one of the step types is T-Sql
why can't you just write the sql in there, or paste it in from the Query Builder?
declare
@thisJobEnabled As Bit
SELECT
@thisJobEnabled=enabled FROM msdb.dbo.sysjobs_view WHERE name=...
IF
@thisJobEnabled = 0
-- disable the rest of the jobs in the sequence and RETURN
ELSE
-- go on to the next job... ?
No? Sorry if I'm off-base here...
>L<
-
lisa,
you are totally correct. unfortunately it is my inexperience
which through you off.
from what you just described things are more clear to me now.
thanks for all your help. this has given me a great starting point.
cheers
_________________________
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply