Send Email In SQL Server

Introduction

A very interesting topic of discussion. We have mail integrated to every application nowadays. We integrate email using SMTP settings in the Web.Config in .NET and use the Send method to send emails. Recently, I came across an interesting challenge, where we were to send emails from our SQL Server. Suppose we have to track the successful scheduled sql query execution. We cannot look into the tables it modified every time in order to check if it actually ran through successfully. It would be so nice, if we could get some kind of notification which can help us know about the status of execution. Yes, it is possible to send mails from our SQL server using few stored procedures which are actually pre-defined. Lets learn how:

Get Started

Remember we will be using predefined Stored procedure to send the mails. Firstly, we need to set up an account with the credentials required by the server to send the emails. Usually the mail is sent through SMTP, Simple Mail Transfer Protocol. The settings would depend on the server your application demands. Remember the configuration needs to be valid. Create a Database Account:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SendEmailSqlDemoAccount'
  , @description = 'Sending SMTP mails to users'
  , @email_address = '[email protected]'
  , @display_name = 'Suraj Sahoo'
  , @replyto_address = '[email protected]'
  , @mailserver_name = 'smtp.gmail.com'
  , @port = 587
  , @username = 'XXXXXX'
  , @password = 'XXXXXX'
Go

Please use proper credentials and server settings in order to successfully deliver the emails, else they will fail and be queued. Next step is to create a profile that would be used to configure the database mail. The sp would look like the following:

EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SendEmailSqlDemoProfile'
  , @description = 'Mail Profile description'
Go

This profile would be used in order to set the mail configuration and the emails and sent. Next step is to map the account to the profile. This will let the profile know, which account credentials it need to work for sending successfully. That would look like the following:

-- Add the account to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SendEmailSqlDemo'
  , @account_name = 'SendEmailSql'
  , @sequence_number = 1
GO

Thus, we are all set to send the emails successfully. The mail sending look up snippet would look like the following:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SendEmailSqlDemo2'
  , @recipients = '[email protected]'
  , @subject = 'Automated Test Results (Successful)'
  , @body = 'The stored procedure finished successfully.'
  , @importance ='HIGH'
GO

The stored procedure being used are sometimes vulnerable to not get executed. So try catch block and Begin and End Transaction are mandatory in few stored procedures. Let's take an example here, Suppose we have a SELECT INSERT query using Stored Procedure, so what happens is we are selecting and inserting from 4 tables, let's say Users | UserLogin | UserEmployment | Departments For each new screen creation we are manipulating and selecting the users based on their PK and inserting again into the same tables with a different FK, representing the particular screen. The query would look like the following:

BEGIN TRY
 BEGIN TRAN
INSERT INTO
  dbo.[User]
SELECT
   us.UserName,
us.UserAddress,
us.UserPhone,
   @fkScreenID
FROM
  dbo.[User] as us
WHERE
  UserID= @userID
COMMIT TRAN
   END TRY
  BEGIN CATCH
 ROLLBACK TRAN
 END
 END CATCH  //Similarly for other tables as well we continue. Its is better to add the Try Catch to whole SP Executing Block

Here, when the transaction in case fails, it would move into the Catch block and there we can have the email sending procedure so as to get a notification regarding the success or failure and the reason where it failed. This would be helpful for any developer.

Troubleshooting Mails

There are also stored procedure to let us know if the mails are successful, failed or remained in the queue. This is a fascinating feature. To check for the mails which were successfully sent and delivered, we run the following query:

select * from msdb.dbo.sysmail_sentitems

Some of the columns it returns are:





In the second image you can see we have the sent_status as sent, which states the mail has been successfully sent. To check for the unsent mails which could not be sent, we run the following query:

select * from msdb.dbo.sysmail_unsentitems

To check for the failed mails, which will not even be retried to be sent from the queue, we run the following query:

select * from msdb.dbo.sysmail_faileditems

For more details on the failure along with the reason, the troubleshoot query would be like the following:

SELECT items.subject,
    items.last_mod_date
    ,l.description FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
GO

The results look like following:



The error description above is like "No Such Host" Error. This error usually comes when we have some SMTP server connection settings wrong. We need to troubleshoot that on our own and recheck the settings credentials and then try. If then it does not seem to work, we need to look for the DNS server settings and retry with the configuration again. Nothing to worry for this though.

Conclusion

Thus we discussed here about sending mails from our own SQL using the stored procedures and how helpful they can prove to be. Troubleshooting the errors is very easy here. Exceptions and errors are a part of development which cannot be avoided but handling them is a challenge and developers can easily do that.

References

• Email Architect MSDN