Send error messages through email using DatabaseMail when SQL Job fails/succeeds

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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:    &#39;GenerateJVForLabourAndOverheads_R11Testing&#39; 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,

Thanks,

Lok..

Posted 19-Dec-12 21:25pm

Lokesh Zende

Updated 19-Dec-12 21:30pm

André Kraak

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Lokesh Zende · Accepted Answer · 2012-12-20T19:13:00

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.