If you are searching for the solution regarding SQL CLR Send an email, you came to the right place. Recently, we need to send notification emails to the customer from SQL Express 2012 Database. However, the latest SQL Server Express editions got deprecated send emails from the SQL server.

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

First of all, here is the 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 = ‘[email protected]’, @subject = ‘Email from SQL Express’, @from = ‘[email protected]’, @body = ‘This is a test email using CLR Assembly’

Source : MSDN

Let me know if you are facing issues regarding SQL CLR Send email. Happy Coding !

If you liked this article, then please subscribe to our Blog for more updates like this. You can also connect me on Twitter, LinkedIn and GitHub.