Dynamic SQL Job

I am trying to create a sql job that has multiple steps, emails out the error on a failure, and will continue to the next step. We can not put Outlook on our servers so we can not use SQL Operators.

Basically I would like every step in the job to jump to a failure step that emails out an error message and returns to the step after the step that failed. I could have a failure step for each actual step, but this job has a number of steps and being the anal programmer I am, I don't want to have multiple steps that do the same thing.

I have figured out how to Dynamically update the current sql Job by using sp_update_jobstep and job tokens [STEPID] and [JOBID]. For example the following code will update the job so it will quit job with failure if the step finishes successfully.

declare @.tmpJobID as uniqueidentifier
declare @.tmpStepID as integer

set @.tmpJobID = (convert(uniqueidentifier, [JOBID]))
set @.tmpStepID = [STEPID]

exec sp_update_jobstep @.job_id=@.tmpJobID, @.step_id=@.tmpStepID, @.on_success_action=2

The problem is that the job is not updated until it is completed. So, the changes will not take affect until the next time it is ran. Is there a way to reload the job into memory, or force the job to use the updates?

I have also tried directly updating the sysjobsteps table but that didn't work either.

Thanks much.Well, does anyone know how to get a job to dynamically jump to a specified step?

Thanks much|||Built-in step dependency has limited complexity capabilities. You'll probably haveto merge the steps and...code away!