[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

SQL server Agent job , using steps to add job

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?

  • 2
1 Solution
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

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

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now