Database Mail, Part 4 - Sending an e-mail

In the previous parts of this series, I have enabled Database Mail, created an account and created a profile. With everything now set up, it is important to test the ability to send an e-mail.

At this point, I am assuming that the SMTP server has been set up and is working properly. SQL Server will record any messages sent returned from the SMTP server as we will see, but once the message has been delivered to the SMTP server it is no longer the responsibility of Database Mail.

To send an e-mail I will use the sp_send_dbmail stored procedure. I am not going to explain all of the parameters that are available in this stored procedure in the this post as I have others in the pipeline that will use this procedure more fully.

For this post I will demonstrate the use of the stored procedure and introduce some of the views that can be used to diagnose any issues with the process.

Step 1 – Sending an e-mail

The following script will send a simple e-mail and return the resulting ID for the new mail item:

DECLARE
     @RV            INT
,    @MailItemID    INT
;

EXEC @RV = msdb..sp_send_dbmail
     @recipients      = 'test@domain.com'
,    @subject         = 'Test Subject'
,    @body            = 'TestBody'
,    @profile_name    = 'TestProfile'
,    @mailitem_id     = @MailItemID OUTPUT
;

SELECT
     @RV
,    @MailItemID

Step 2 – Checking the status of the email

The mail item ID can be used to track the status of the e-mail, and to retrieve details of any issues that may have occurred:

DECLARE
     @MailItemID    INT = 10
,    @SentStatus    VARCHAR(8)
;
SELECT
     @SentStatus = SMAI.sent_status
FROM msdb..sysmail_allitems SMAI
WHERE
     SMAI.mailitem_id = @MailItemID
;

IF @SentStatus = 'failed'
BEGIN
    SELECT
        SMFI.*
    FROM msdb..sysmail_faileditems SMFI
    WHERE
        SMFI.mailitem_id = @MailItemID
    ;

    SELECT
        SMEL.*
    FROM msdb..sysmail_event_log SMEL
    WHERE
        SMEL.mailitem_id = @MailItemID
    ;
END
ELSE
BEGIN
    SELECT
        SMAI.*
    FROM msdb..sysmail_allitems SMAI
    WHERE
        SMAI.mailitem_id = @MailItemID
    ;
END

If the e-mail has failed then a record of the item will be displayed, along with the entries from the event log that were generated by Database Mail in relation to the mail item. Messages from the SMTP server will be displayed in this recordset and should be used as the first step in diagnosing any issues.

If the e-mail was sent successfully, or Database Mail is still trying to send the e-mail then one record will be returned that contains all of the details related to the item.

In Part 5, I will describe the monitoring of the Database Mail system and some common issues to look out for.