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

Question

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.

Frolicsome

OK. Did you have a question to ask us? – Derman

So which bit are you struggling with? – Vidette

It will depend entirely on the DBMS in use. The commands to do that for one DBMS are completely irrelevant to any other DBMS. – Beaulahbeaulieu

@Siva: ah, yes...the old 'hide the important information in the title and not put it in the question body or the tags' trick. Thanks for updating the tags. – Beaulahbeaulieu

Answer 1

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.

Owner

Answer 2

Typically I use the On Success/On Failure to control the flow. If you have different branches, at the end of each "branch" there has to be a NO-OP step which goes to the continuation:

Step 1 - Some Op - On Failure Goto Step 4, On Success Go to next step
Step 2 - Some Op
Step 3 - Goto continuation step
Step 4 - Some op
...
Step n - Continnuation step
Step n+1 - finish the common processing

Needless to day, managing this linear flow with GOTO is not grewat for complex logic flows.

http://www.sqlservercentral.com/articles/Stairway+Series/72457/

Caddoan

Answer 3

Place the following code in Step 1 of the job. It checks to see if it is Primary (specify the name of the DAG) and stops the job if it is not.

-- Do I wish this job to continue or not...
-- e.g. Is this the Primary in the DAG
-- If not, stop the job
DECLARE @rc int; 
EXEC @rc = dbo.fn_hadr_group_is_primary N'DAG_001';

IF @rc = 0
BEGIN;
    DECLARE @JobID uniqueidentifier
    SELECT @JobID = $(ESCAPE_NONE(JOBID));
    EXEC msdb.dbo.sp_stop_job @job_id = @JobID;
END;

Ezar

Please try to explain your answers so people can learn from them – Bodwell