Configure Database Mail – Send Email From SQL Database

The process of SQL Server Database Mail configuration has three main steps i.e.

    1. Create a Database Mail account
    2. Create a Database Mail profile
    3. Configure those two to work together

How to send Email from Trigger in SQL Server? 

Enabling Database Mail

USE MASTER

GO

SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

SP_CONFIGURE 'Database Mail XPs', 1

RECONFIGURE WITH OVERRIDE

GO

SP_CONFIGURE 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO

 


Mail Account Setup

EXEC msdb.dbo.sysmail_add_account_sp

    @account_name = 'Test'

   ,@description = 'Send emails using SQL Server Stored Procedure'

   ,@email_address = 'anil.singh581@gmail.com'

   ,@display_name = 'Test'

   ,@replyto_address = NULL

   ,@mailserver_name = 'smtp.gmail.com'

   ,@username = 'anil.singh@gmail.com'

   ,@password = 'YourPassword#12345'

   ,@port = 587

   ,@enable_ssl = 1

GO

 

EXEC msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Test'

   ,@description = 'Send emails using SQL Server Stored Procedure'

GO

 

EXEC msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Test'

   ,@account_name = 'Test'

   ,@sequence_number = 1

GO

 

Send the Email

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Test'

   ,@recipients = 'anil.singh581@gmail.com'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO

 

Delete Account Name from DB mail

/* EXECUTE msdb.dbo.sysmail_delete_account_sp  @account_name = 'Test' ; */

 

See the Status of Email

SELECT * FROM msdb.dbo.sysmail_unsentitems

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_faileditems

 

SELECT mailitem_id

    ,[subject]

    ,[last_mod_date]

    ,(SELECT TOP 1 [description]

            FROM msdb.dbo.sysmail_event_log

            WHERE mailitem_id = logs.mailitem_id

            ORDER BY log_date DESC) [description]

FROM msdb.dbo.sysmail_faileditems logs

These logs have all useful information that could help in troubleshooting, and we are in particular interested in the ‘description’ column as it holds detailed information about the error and what went wrong.


ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^