running sql agent job step based on value returned by a query
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.
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.
ASKER
Aneesh, thanks for answering. Can you please give me an example for how to set Process Exit Code. Google didnt help much.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
setting @raise error worked.
You may need to alter the step to return a different value in case it is not successful using a return statement.