Solved

running sql agent job step based on value returned by a query

Posted on 2015-01-07
5
99 Views
Last Modified: 2015-01-21
I am creating a sql agent job in MS SQL server 2008 R2.
First step runs a query to get validation status from the database. I want to run the second step only if validation_status = 1, or quit the job returning failure if validation_status <>1
One way of accomplishing this is to create a new job with the second step and use an if statement in step1 to run the second job.
Is there a better way where I can I accomplish this in the same job, to run the second step based on the outcome of query on first step?

Thank you for your help.
0
Comment
Question by:patd1
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40535919
You can set the value of  "Process Exit code of a successful command " for the fist step, by default it is zero.
You may need to alter the step to return a different value in case it is not successful using a return statement.
0
 

Author Comment

by:patd1
ID: 40537940
Aneesh, thanks for answering. Can you please give me an example for how to set Process Exit Code. Google didnt help much.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40538030
Goto job properties -> double click on step1 of the your job, just under the "Run As " selection box, you can see Process Exit Code text box
0
 

Accepted Solution

by:
patd1 earned 0 total points
ID: 40538173
I used @raise_error and raised error if validation_status <> 1

This works for me.

Thank you for your help.
0
 

Author Closing Comment

by:patd1
ID: 40561569
setting @raise error worked.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

15 Experts available now in Live!

Get 1:1 Help Now