Why is the SQL Agent job not failing when there's an error in a step?

111 Views Asked by At

There're several scheduled SQL Agent jobs in my 2019 Server. Each job-One Stored Procedure which I usually call a container consists of several Stored Procedures similar to below.

SQL_Agent_Job_1

Create Procedure SQL_Agent_Job_1 as
BEGIN
    exec Stored_proc1
    exec Stored_proc2
    exec Stored_proc3
    exec Stored_proc4
END

SQL_Agent_Job_2

Create Procedure SQL_Agent_Job_2 as
BEGIN
    exec Stored_proc5
    exec Stored_proc6
    exec Stored_proc7
    exec Stored_proc8
END

I need the job to fail if it runs into an error at either of the steps immediately, say for instance if Job 1 runs into a data overflow error in StoredProc2, I'd want the job 1 to fail without executing StoredProc3 and 4. This is the case in one of my jobs but isn't in a few.

So job 2, even if there's a invalid column name error in StoredProc6 and wasn't able to complete all the actions it's supposed to such as inserting some rows to a table, will ignore that move on to StoredProc7 and 8 and afterwards generates an error with job 2 failed. this makes it really difficult to track at which point exactly the job failed.

I'd like to understand what's causing this behavior and what changes should be made in order for a job to fail instantly when an error is encountered.

0

There are 0 best solutions below