Results 1 to 6 of 6

Thread: how can i change the step status to "failed"

  1. #1
    Join Date
    Sep 2007
    Posts
    14

    how can i change the step status to "failed"

    Hi friends,
    please help me with my urgent needs.
    I have created a job. This job contains 6 steps.
    All steps are sql querries.
    In step no:3 i have an if condition in the script.
    When the condition becomes true i have to run the script.
    That is ok.
    But the problem is when the condition becomes falls i have
    to go to the step no 6 and at the same time the status
    of step no:3 should be like "failed".

    can you help me please
    Please this is an urgent reqirement.

    Thank you.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try mark the step failure in your script, and modify step's on failure to jump to step 6.

  3. #3
    Join Date
    Sep 2007
    Posts
    14
    Hi rmiao,
    i dont understand what you are saying. I have to make the status of the step failed by the script.
    For eg: if a=b
    backup database northwind to disk=D:\backup\nw.bak
    else
    here i want to make the step failed. how can i do that.
    please help me.
    I tried many ways. Every thing works well. But when we look on the view status of the job it does not look like 'failed'.

    Ichayan

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You need to modify msdb..sysjobsteps table to mark step failed.

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    You can manually force a job failure by using RAISERROR.

    First, you need to add an error message. Find out what available error numbers you can use by running this query:

    SELECT TOP 10 * FROM master..sysmessages ORDER BY error DESC

    If you have never added a message to the sysmessages table before, most likely the largest error number in your table will be something like 21695 (or 33xxxx for SS 2005). User error messages start at 50000, so you can add an error message like this:

    EXEC sp_addmessage 50001, 16, N'Forced job step failure'

    Then, to call this error message (and therefore create a failure), execute this statement:

    RAISERROR(50001, 16, 1)

    See Books Online for more explanation about the use of error messages.

  6. #6
    Join Date
    Sep 2007
    Posts
    14
    Hi Friends,
    Thank you so much for your great help.

    Ichayan

Posting Permissions

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