I use the SQL agent a lot, and it is handy for a lot of things, but it can be frustrating to not be able to pass state information between steps.
For example, I have a job where I want to execute data verification steps against multiple tables. It makes sense to have the check for each table in its own step with a clear label to simplify troubleshooting– so when the job fails, you can see which step had an error and know from the name exactly what’s wrong. But I want all steps in the job to run, regardless of whether a step fails— I want to check for failure at the end.
The most basic way to do this is to have each job step log to a table. This isn’t really bad, but I’d rather not maintain a table for every job of this type. It leaves room for failure, it’s more to maintain, and it just feels redundant for the most part: all of the job history is tracked in MSDB anyway, shouldn’t I be able to use that?
Well, I think I can… [read on for sample code…]