Sending Email Using CLR Stored Procedure

Recently ,  we need to send notification emails to customer from SQL Epress 2012 Database – but the latest SQL Server Express editions got deprecated send email from SQL server

Little search around turns into great help and finally I found a way to get it done is , create CLR Store procedure and few configurations make you enable to send emails !  Source : MSDN

Here is series  of steps involved to get it done !

Step 1:  Write Block of code in your favorite language C# or VB
C# Block

    using System.Net;
    using System.Net.Mail;

    public class StoredProcedure
    {
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void spSendMail(string recipients, string subject, string from, string body)
    {
    using (MailMessage mailMessage = new MailMessage(from, recipients))
    {
    mailMessage.Subject = subject;
    mailMessage.Body = body;
    mailMessage.IsBodyHtml = true;
    SmtpClient smtpClient = new SmtpClient(“YOUR SMTP SERVER”);
    smtpClient.Credentials = new NetworkCredential(“EMAIL ADDRESS”, “PASSWORD”);
    smtpClient.Port = YOUR SMTP PORT;
    smtpClient.UseDefaultCredentials = false;
    smtpClient.EnableSsl = true/false (Depands on your SMTP Setting);
    smtpClient.Send(mailMessage);
    }
    }
    }

VB Block

    Imports System.Net
    Imports System.Net.Mail

    Public Class StoredProcedure
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spSendMail(ByVal recipients As String, ByVal subject As String, ByVal from As String, ByVal body As String)
    Dim mySmtpClient As SmtpClient

    Using myMessage As New MailMessage(from, recipients)

    myMessage.Subject = subject
    myMessage.Body = body

    myMessage.IsBodyHtml = True

    mySmtpClient = New SmtpClient(“YOUR SMTP SERVER”)
    mySmtpClient.Credentials = New NetworkCredential(“EMAIL ADDRESS”, “PASSWORD”)
    mySmtpClient.Port = YOUR SMTP PORT
    mySmtpClient.UseDefaultCredentials = false

    mySmtpClient.EnableSsl = true/false (Depands on your SMTP Setting)
    mySmtpClient.Send(myMessage)
    End Using
    End Sub
    End Class

Step 2: Compile your code using command line
C# Block

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc /target:library C:\Users\Administrator\Desktop\SendEmail.cs

VB Block

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>vbc /target:library C:\Users\Administrator\Desktop\SendEmail.vb

Step 3: Register your assembly

    USE msdb
    GO

    CREATE ASSEMBLY SendEmail FROM ‘C:\SendEmail.dll’
    WITH PERMISSION_SET = UNSAFE
    GO

    CREATE PROCEDURE [dbo].[spSendMail]
    @recipients [nvarchar](4000),
    @subject [nvarchar](4000),
    @from [nvarchar](4000),
    @body [nvarchar](4000)
    WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [SendEmail].[StoredProcedure].[spSendMail]

   

Step 4: Enable CLR Configuration

    sp_configure ‘clr enabled’,1
    GO
    RECONFIGURE
    GO
    sp_configure ‘clr enabled’ — make sure it took
    GO

Step 5: Drop Assembly / Store Procedure (Optional)

    USE msdb
    GO
    DROP PROCEDURE dbo.spSendMail
    GO
    DROP ASSEMBLY SendEmail
    GO

Finally , to test your CLR Store procedure

    EXEC msdb.dbo.spSendMail @recipients = ‘recipients@recipients.com’, @subject = ‘Email from SQL Express’, @from = ‘info@vnypatel.com’, @body = ‘This is a test email using CLR Assembly’

Let me know if you are facing issues sending your mail out.

Happy Coding !