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:

1 DECLARE 2 @RV INT 3 , @MailItemID INT 4 ; 5 6 EXEC @RV = msdb..sp_send_dbmail 7 @recipients = '' 8 , @subject = 'Test Subject' 9 , @body = 'TestBody' 10 , @profile_name = 'TestProfile' 11 , @mailitem_id = @MailItemID OUTPUT 12 ; 13 14 SELECT 15 @RV 16 , @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:

1 DECLARE 2 @MailItemID INT = 10 3 , @SentStatus VARCHAR(8) 4 ; 5 SELECT 6 @SentStatus = SMAI.sent_status 7 FROM msdb..sysmail_allitems SMAI 8 WHERE 9 SMAI.mailitem_id = @MailItemID 10 ; 11 12 IF @SentStatus = 'failed' 13 BEGIN 14 SELECT 15 SMFI.* 16 FROM msdb..sysmail_faileditems SMFI 17 WHERE 18 SMFI.mailitem_id = @MailItemID 19 ; 20 21 SELECT 22 SMEL.* 23 FROM msdb..sysmail_event_log SMEL 24 WHERE 25 SMEL.mailitem_id = @MailItemID 26 ; 27 END 28 ELSE 29 BEGIN 30 SELECT 31 SMAI.* 32 FROM msdb..sysmail_allitems SMAI 33 WHERE 34 SMAI.mailitem_id = @MailItemID 35 ; 36 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.