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:
- Define a SQL Agent job with no steps
-
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)
)
Best Answer
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:
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!