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 = 'email@example.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.