3 Easy Steps to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. 

I am going to use Database Mail to Configure Automation Failure Notification..

1. Create an e-mail profile, specify its SMTP accounts and Specify profile security.
2. Configure Operator.
3. Assign Operator on Jobs/Schedulers.

In First Step: Create an e-mail profile, specify its SMTP accounts.

To Varify check TSQL  

use msdb

SELECT *  FROM [msdb].[dbo].[sysmail_profile]

Now Enable Database Mail service.

USE master

Go

EXEC sp_configure 'show advanced options', 1

Go

RECONFIGURE

Go

EXEC sp_configure 'Database Mail XPs', 1

Go

RECONFIGURE

Go

EXEC sp_configure 'show advanced options', 0

Go

RECONFIGURE

Go

In 2nd Step : Configure Operator.

To Varify TSQL  

use msdb

SELECT *   FROM [msdb].[dbo].[sysoperators]

In 3rd Step : Configure a Job scheduled Task..

              

Select email check box and then profile and event type like on success, or fail etc…

To Varify TSQL  

use msdb

SELECT job_id,name,notify_email_operator_id   FROM [msdb].[dbo].[sysjobs]

Restart  Agent Service and wait for the mail notification…….

Result : ---

From: "System"

To: <sumank@tulip.net>

Sent: Sunday, February 19, 2012 8:40 AM

Subject: SQL Server Job System: 'Prefeasiblity_upload_new' completed on \\XXX\REPORTING

 JOB RUN: 'Prefeasiblity_upload_new' was run on 2/19/2012 at 8:40:00 AM
 DURATION: 0 hours, 0 minutes, 34 seconds
 STATUS: Failed
 MESSAGES: The job failed.  The Job was invoked by Schedule 28 (pref).  The last step to run was step 1 (sp_preorder_survey_upload_2_new)

.

Wow..... I Received Mail, mean Successfully Configured but so sadddddddddd one more procedure FAIL.........Going to check my ERROR ...................

Thank You .. Have a gr8 days...........

@SumnJha