Click here to Skip to main content
15,850,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi ,

I have setup a profile and account for sending mail using DatabaseMail.
I have a SQL job and if the job fails, I receive an Email.

Till this point, everything works fine. This is what the email looks like :
JOB RUN:    'GenerateJVForLabourAndOverheads_R11Testing' was run on 20-12-2012 at 10:10:50
DURATION:   0 hours, 0 minutes, 0 seconds
STATUS:     Failed
MESSAGES:   The job failed.  The Job was invoked by Schedule 58 (Execute SP).  The last step to run was step 1 (Execute SP).

Now what I would like is, if the job fails for any reason, that error message should appear in the email message body.

For example, FK_Constraint erro , Conversion errors or any error that caused my SQL job to fail.

Is this possible?

Any help appreciated,


Updated 19-Dec-12 22:30pm

1 solution

My sql job had only one step.So I added another step to it and in step 2 of the job, I added the below code

declare @body1 varchar(8000)
select top 1 @body1 = [Message] from msdb.dbo.sysjobhistory A INNER JOIN msdb.dbo.sysjobs B ON B.Job_id = A.Job_id
 where B.Name = <<JobNaMe>> and A.step_id =1 order by instance_id desc
 EXECUTE msdb.dbo.sp_send_dbmail @profile_name = <<YourMail profile>>
,@recipients = <<RecipeintEmail>>,@subject =<<Your subject>>,@body = @body1

It works like a magic.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900