Send email with job step's failure message from within the job itself

Question

Hi everybody,

I'm wondering if it's even possible to setup a job that will try to execute something and if it fails, it will send information about failed step directly from the job?

I read this excellent blog post

https://www.sqlshack.com/reporting-and-alerting-on-job-failure-in-sql-server/

I tried to simplify everything and right now I'm using this code which seems to be sending information about previously failed job step instead of the job I'm executing:

declare @LastStepErrorMessage varchar(max) = (select top(1) message FROM MSDB.dbo.sysjobhistory h
 WHERE h.run_status = 0
 AND h.step_id > 0 order by run_date DESC);

I think the problem is that the job didn't complete yet when I'm trying to send the email from that same job itself. Do you see a solution to this problem or how should I code getting information about failed step(s) in the currently executing job?

Thanks a lot in advance.

Answer 1

The way you have written the query, you will get the message for any failed job on that same day. You need at least add run_time to the ORDER BY clause.

It can also be a safety measure to restrict the query to the current job. You don't have to hard-code the job name, but you can use a job token for this. See https://learn.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?redirectedfrom=MSDN&view=sql-server-ver15.

Answer 2

Rather than doing it that way, I use a process like this. Setup a job which runs a proc every 5 mins and emails the results of all jobs.

https://www.sqlshack.com/how-to-create-email-alert-sql-server-agent-for-job-failures/

Answer 3

I already switched the code, but I'll re-try later today. For each of the tokens I tried I was getting 'Variable A-SVR (or other names not found' when I was trying to run the job manually.

What do you mean with "run manually"? If you run the code in a query window, it will not work, as this token replacement is something Agent does.

So unfortunately the message is defined as nvarchar(4000) in the sysjobhistory table and therefore it is truncated (and not sure why SSIS returns so much extra unhelpful verbiage).

It is usually best to redirect output to a file, which you do on the Advanced page of the job step definition. In theory, you could have the second step to read that file and mail it. That would better be a PowerShell step, I think.

Or you could opt to send the truncated error message, and add a line to refer to the full log file. (Of which the name is in sysjobsteps.)

Answer 4

You should add another step for failed notification in the same job.
On failure, let the job execute the failed notification step and I believe ( not tested ) you can use script to read the failed step info.