Send mail using CLR Stored Procedure

Send mail using CLR Stored Procedure

In SQL server you have two ways how to send mail using T-SQL, first is xp_sendmail and second is Database Mail. But this functionality isn't in Express edition, how allow it describes article here.
This article describes step by step how to implement own SendMail procedure using CLR stored procedures.

Creating CLR procedure

First step is implementing CLR code of our stored procedure. Implementation is pretty easy.
Procedure parameters:
- smtpServer - server address
- smtpUser - user name
- smtpPasswd - user password
- recipients - recipients
- subject - email subject
- from - sending address
- body - email body

SendMail.cs

using System.Net;
using System.Net.Mail;
using Microsoft.SqlServer.Server;
 
namespace MsSqlServerProcedures
{
    public class SendMail
    {
 
        [SqlProcedure()]
        public static void spSendMail(string smtpServer, string smtpUser, string smtpPasswd, string recipients, string subject, string from, string body)
        {
            SmtpClient mySmtpClient = null;
 
            using (var myMessage = new MailMessage(@from, recipients))
            {
 
                myMessage.Subject = subject;
                myMessage.Body = body;
                myMessage.IsBodyHtml = true;
 
                mySmtpClient = new SmtpClient(smtpServer);
                mySmtpClient.Credentials = new NetworkCredential(smtpUser, smtpPasswd);
                mySmtpClient.Send(myMessage);
                if (SqlContext.Pipe != null) SqlContext.Pipe.Send("Message was sent succesfully.");
            }
        }
 
    }
}

Compiled library copy on the SQL server (eg C:\MSSQL\MsSqlServerProcedures.dll)

Extension using ConfigurationManager

Is possible to use ConfigurationManager class for storing SMTP server address and login, but you have to save your config file as sqlservr.exe.config in the \Binn folder under the root path of installed SQL Instance.

Using ConfigurationManager

using System.Configuration;
 
...
 
public static void spSendMail(string recipients, string subject, string from, string body)
 
...
 
var smtpServer = ConfigurationManager.AppSettings["SMTPserver"];
var userName = ConfigurationManager.AppSettings["UserName"];
var passwd = ConfigurationManager.AppSettings["Passwd"];
 
...

SQL server deployment

Next step is to add assembly to SQL server and to create stored procedure.

DeploymentScript

/* Deployment Script */
ALTER DATABASE Your_Database SET trustworthy ON -- allows access resources outside the database
 
CREATE ASSEMBLY SendEmail FROM 'C:\MSSQL\MsSqlServerProcedures.dll'  
WITH PERMISSION_SET = UNSAFE  
GO 
 
-- Create Procedures
 
CREATE PROCEDURE [dbo].[spSendMail]
   @smtpServer [nvarchar](255),
   @smtpUser [nvarchar](255),
   @smtpPasswd [nvarchar](255),
   @recipients [nvarchar](4000),
   @subject [nvarchar](4000),
   @FROM [nvarchar](4000),
   @body [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SendEmail].[MsSqlServerProcedures.SendMail].[spSendMail]
 
GO
 
-- Enable CLR
EXEC sp_configure 'show advanced options' , '1';
reconfigure;
GO
EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;
GO
EXEC sp_configure 'show advanced options' , '0';
reconfigure;
GO

Testing

If our procedure is deployed, we can test it.

TestScript

EXEC spSendMail @smtpServer = 'smtp.server.com',
				@smtpUser = 'userName',
				@smtpPasswd = 'password',
				@recipients = 'someone@address.com', 
				@subject = 'Email from SQL Express', 
				@FROM = 'me@server.com', 
				@body = 'It wokrs! This is an email from your SQL Server!'

Removing

RemoveScript

/* Removes procedures and assemblies */
DROP PROCEDURE dbo.spSendMail
GO 
DROP ASSEMBLY SendEmail 
GO

Table of Contents

Send mail using CLR Stored Procedure

Creating CLR procedure

Extension using ConfigurationManager

SQL server deployment

Testing

Removing

References