Results 1 to 3 of 3

Thread: Dynamic SQL Job

  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Unanswered: 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.

    PHP Code:
    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=
    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.
    Last edited by Ten_Spoons; 10-25-04 at 17:26.

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    Well, does anyone know how to get a job to dynamically jump to a specified step?

    Thanks much

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Built-in step dependency has limited complexity capabilities. You'll probably haveto merge the steps and...code away!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •