Sending Email Using Stored Procedures in SQL Server

Introduction

This is a very interesting topic of discussion. We have mail integrated to every application now a days. We integrate email using SMTP settings in the Web.Config in .NET and use the Send method to send mails. 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.
Let's learn how:

Get Started

Remember we will be using a pre defined Stored procedure to send the mails. First of all, we need to set up an account with the credentials required by the server to send the mails. 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 = 'suraj.0241@gmail.com'
  , @display_name = 'Suraj Sahoo'
  , @replyto_address = 'suraj.0241@gmail.com'
  , @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 mails, else they will fail and be queued.

The next step is to create a profile which would be used to configure the database mail. The stored procedure would look like below:

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 sent.

The next step is to map the account to the profile. This will let the profile know, which account credentials it needs to work for sending successfully.
That would look like:

-- 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 successful emails. The mail sending look up snippet would look like below:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SendEmailSqlDemo2'
  , @recipients = 'suraj.0241@gmail.com'
  , @subject = 'Automated Test Results (Successful)'
  , @body = 'The stored procedure finished successfully.'
  , @importance ='HIGH' 
GO

The stored procedures being used are sometimes vulnerable to not getting executed. So Try catch block and Begin and End Transactions 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 below:

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. 
             //It 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 reason and where it failed. This would be so helpful for any developer.

Troubleshooting Mails

There are also stored procedures 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 below 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 below 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 below query:

select * from msdb.dbo.sysmail_faileditems

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

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 below:

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 and the set as well.

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

History

• 8^th September, 2015: Initial version

CodeProject