How to skip a job step based on outcome of previous SQL Agent job step?

sql-server tsql sql-agent-job
user1366888 picture user1366888 · May 1, 2012 · Viewed 27.3k times · Source

I'm trying to create a job that will run based on the online status of a database. For example, step 1 will check if the database is online; if the database is online, it will run the rest of the steps, otherwise, it will report the job as successful.

Answer

Aaron Bertrand picture Aaron Bertrand · May 1, 2012

Well you could set step 1 to be:

DECLARE @dbState TINYINT;
SELECT @dbState = state FROM sys.databases WHERE name = N'dbname';
IF @dbState = 0
BEGIN
  RAISERROR('Database is online.', 11, 1);
END

Set the properties of step 1 to be:

  • on success, go to step 2
  • on failure, go to step N

Step 2 -> n-1 would do their normal things. Step n-1 might quit the job with success on success, or move to step N on success.

Step N could be as simple as:

PRINT 1;

...and would be set to quit the job with success.

Related questions

How to pass a parameter to a SQL Job that will execute a stored procedure

I have the below code (only the portion that is needed) EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SomeStep', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_…

Read More
Create SQL Server Agent job for stored procedure with input parameter

Could you suggest please how to create SQL Server Agent job for a stored procedure that have 1 input parameter? The procedure is correctly created and i executed it using this code : EXECUTE dbo.MYProcedure N'2016-02-25'; Is there …

Read More
How to accurately detect if a SQL Server job is running and deal with the job already running?

I'm currently using code like this to detect if a SQL server job is running. (this is SQL Server 2005, all SP's) return (select isnull( (select top 1 CASE WHEN current_execution_status = 4 THEN 0 ELSE 1 END from openquery(devtestvm, 'EXEC msdb.dbo.…

Read More