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
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