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 !