Sending Emails with CLR + SQL Server Express Editions

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

Read More