SQL Server Agent Error | How to get Notified if a Job is Successful or Fail

Question

Hello everyone,

I wish I knew how to fix this, search the web are there are so many conflicting reasons as to why this is happening.

I have MS Outlook 2010 Installed and running. I have also successfully set up and tested Database Mail and that also working.

All I am trying to do is get an email when a Job has completed or failed.

Here is a screen of the error:



Can someone please help me?

I am losing it!

Thank everyone!

Answer 1

Hi Synthologic,

Have you configured a mail profile for SQL Server Agent? If not, please pay attention to achieve it: Configuration of mail - error 22022: sqlserveragent error: the mapi call failed.

For more information: How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions. The configuration will be similar in SQL Server 2008 or R2.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Synthologic,

Have you configured a mail profile for SQL Server Agent? If not, please pay attention to achieve it: Configuration of mail - error 22022: sqlserveragent error: the mapi call failed.

For more information: How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions. The configuration will be similar in SQL Server 2008 or R2.

---

Stephanie Lv

TechNet Community Support

Thank you Stephanie,

The solution was very simple. All I had to do was run a stored procedure as indicated on this page:

http://msdn.microsoft.com/en-us/library/ms190307.aspx


Here is what I did:

1. Create/Setup Database Mail

2. I created a Job

3. For step 1 I added the code that I needed to run

4. I added this piece of code to send the email if step one is successful.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Server Administrator',
    @recipients = '[e-mail]',
    @body = 'ETL completed successfully without errors.',
    @subject = 'ETL Successfully Completed';

All you do is copy the same code for another step and direct job agent to go to that step if the job fails.

To answer your question, yes I did set up DB Mail.

However, I was trying to set up mail by creating which just didn't work.

I will mark your responds as answered because it's also a workable solution.

Thanks again!

Synth