SQL Server - TRY/CATCH does not work in certain cases

Question

How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

For example, this does not work:

1. Define a SQL Agent job with no steps

2. Try to start the job inside a TRY/CATCH

   BEGIN TRY
       EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
   END TRY
   BEGIN CATCH
       SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
   END CATCH
   

Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.

Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?

It also bypasses my CATCH block if the job is busy handling a request (I'm spamming stop and start requests in my testing).

Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.

Related case: invalid linked server reference

P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.

P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?

*_{What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20))}

Answer 1

How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

You can't, unfortunately.

SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server

The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify.

Erland covers that issue here:

SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.

I know you mentioned you're not interested bespoke solutions that aren't broadly applicable, but I think it's worth mentioning that, when dealing with Microsoft-shipped stored procedures, one solution to this TRY / CATCH issue is to always capture the return code of the procedure. If it's non-zero, you can manually raise an error to trigger the CATCH block (thanks Dan Guzman).

* brief, ha!