Home > Uncategorized > Alternative to sp_send_dbmail

Alternative to sp_send_dbmail

If you’d like to send email from SQL server, but don’t, or can’t setup sp_send_dbmail on your SQL server database, then you can use this CLR stored procedure, which uses Mailgun as an outbound SMTP server.

using System;
using System.Net;
using Microsoft.SqlServer.Server;
using System.Net.Mail;
public partial class StoredProcedures
{
/// <summary>
/// Note: you will need to set
/// EXEC sp_dbcmptlevel ‘GeneralPurpose’, 90
/// ALTER DATABASE GeneralPurpose SET TRUSTWORTHY ON
/// </summary>
/// <param name=”subject”>The subject.</param>
/// <param name=”body”>The body.</param>
/// <param name=”to”>To.</param>
[SqlProcedure]
public static void CLR_SendEmail(string subject, string body, string to)
{
SmtpClient smtpClient = new SmtpClient();
NetworkCredential basicCredential =
new NetworkCredential(“postmaster@xxxx.mailgun.org”, “xxxx”);
MailMessage message = new MailMessage();
MailAddress fromAddress = new MailAddress(“postmaster@xxx.mailgun.org”);

smtpClient.Host = “smtp.mailgun.org”;
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = basicCredential;

message.From = fromAddress;
message.Subject = subject;
//Set IsBodyHtml to true means you can send HTML email.
message.IsBodyHtml = true;
message.Body = body;
message.To.Add(to);

try
{
smtpClient.Send(message);
}
catch (Exception ex)
{
//Error, could not send the message
SqlContext.Pipe.Send(ex.Message);
}
SqlContext.Pipe.Send(“Sent” + Environment.NewLine);
}
};

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: