SQLCache: SQL Server Database Mail

Database Mail is a great feature for sending email messages and notification alerts from SQL Server Database Engine. Once you configured SMTP settings, database applications can send email messages which may contain query results, include files and notification alerts. Database Mail uses msdb service broker queues to send email messages and logs email activity to msdb database tables.

There are two ways to configure Database Mail. I prefer T-SQL because we run the same setup on multiple servers.

Transact-SQL
Database Mail Configuration Wizard

Example in post shows to configure SQL Server Database Mail using Gmail Account.

Method 1: Transact-SQL

Enable Database Mail

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Create Database Mail Account

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail Account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA',
@email_address = sqlcache@gmail.com', -- change valid email address
@display_name = 'SQL Cache DBA Mail',
@description = 'Database Mail Account',
@mailserver_name = 'smtp.gmail.com' ,
@mailserver_type =  'SMTP' ,
@port =  587 ,
@username =  'sqlcache@gmail.com',  -- change valid email address
@password =  'password',   -- email password
@use_default_credentials=0,
@enable_ssl=1

Parameter	Description
@use_default_credentials	When this value 1, Database Mail uses the credentials of Database Engine.

	When this value 0, Database Mail uses the @username and @password provided with Account creation for authentication on the SMTP server.
@enable_ssl	if SSL is required on your SMTP server. enable_ssl is bit

Create Database Mail profile

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Profile',
@description = 'SQL Cache DBA Profile' ;

Add the account to the profile

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Profile',
@account_name = 'DBA',
@sequence_number =1 ; --- check this

Grant access to the profile to all users

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Grant access to the profile to all users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBA Profile',
@principal_name = 'public', 
@is_default = 1 ;

Verify DBA profile and accounts

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Verify DBA profile and accounts

SELECT * FROM msdb.dbo.sysmail_account  WHERE  name like '%DBA%'
SELECT * FROM msdb.dbo.sysmail_profile WHERE  name like '%DBA%'

Enable less secure apps option on link to receive emails on Gmail account.
Send test email to verify setup is configured correctly

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Send test email

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='DBA Profile',
@recipients='sqlcache@gmail.com',
@subject='Test message',
@body='This is the body of the test message.'

Verify status of the email message

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Check email message status

--Status of the message 
SELECT last_mod_date, case sent_status 
when 1 then 'Sent Successfully' 
when 2 then 'Failed' 
when 3 then 'Unsent' end sent_status ,[subject] ,recipients 
FROM msdb.dbo.sysmail_mailitems 
ORDER BY last_mod_date DESC

--Reason of the message failure 
SELECT top 1  profile_id,items.last_mod_date,items.[subject],l.[description]  ErrorMessage,items.sent_status,recipients
FROM msdb.dbo.sysmail_faileditems as items 
INNER JOIN msdb.dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id
ORER BY last_mod_date DESC

Update the information of existing Database Mail account if required. In my case I entered wrong password while account creation.

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Update existing Database Mail account 

EXECUTE msdb.dbo.sysmail_update_account_sp 
@account_id = 5, --- ID of DBA account
@account_name = null,
@description = null, 
@email_address = null,
@display_name = null, 
@replyto_address = NULL, 
@mailserver_name = null,
@mailserver_type = null, 
@port = null, 
@timeout = null,
@username =  'sqlcache@gmail.com', --change valid email account 
@password =  'emailpassword', -- email password
@use_default_credentials = 0, 
@enable_ssl = 1 ;