Solved

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

Posted on 2015-01-07
5
111 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 36
SQL Replication question 9 42
Help Required 3 96
TSQL - How to declare table name 26 30
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

770 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