SQL server Agent job , using steps to add job

Posted on 2012-09-12
Last Modified: 2012-10-09
I am using SQL agent job with steps. I can see many options and have some queries.

How does failure of a step inside job is determined? Do I need to do a return code?
If I am using capture and masking any errors raised how will it handle failure?

Question by:crazywolf2010
    LVL 12

    Expert Comment

    If you have error handling built into the code of your step, then your step could theoretically never fail.

    The job status could be thought of as
    success = able to run the command
    failure = not able to run the command .

    If you want to raise an error based on a specific condition in your query, then you can use this:

    IF Put Your Boolean Test Here
       RAISERROR('50005 Put your message regarding the failure here',16, -1, @@servername )

    Open in new window


    Author Comment

    I am asking how does steps within job work? How does a success/failure of a step is measured?

    LVL 12

    Accepted Solution

    This is what determines the success or failure of a job step.

    If your code cannot execute, then the job step fails.

    If your code executes, but doesn't necessarily do what you want it to do, the job succeeds.

    In  the case of SSIS packages set up as job steps, the designer sets the number of acceptable errors within a package before the package will fail (thus failing the job step).

    In the case of SQL code, if the sql code has an error and does not execute, the job step fails.

    If this still doesn't answer your question, can you restate it?

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now