Wait...what? We all know that sending E-Mail from SQL 2008 Express is not possible without a third party component, right? Wrong. It is a common problem that most of us face while trying to build various lightweight applications. We wire them up to SQL Server Express and then we are not able to send email. However, there is an answer.
This solution is not entirely new, in fact there is an article that describes a very basic approach to this and can be found here: http://www.mssqltips.com/tip.asp?tip=1795. The solution works for simple use cases, but didn't do everything I needed it to, so I went ahead and added to it.
For those of you not familiar with using the Common Language Runtime (CLR), it is basically a way in which to execute, or call, managed code written in C# or VB.NET from within the SQL Server environment. This is basic functionality that is bundled in all versions of SQL (from 2005 on), including Express. There are two parts to this solution:
The source VB file.
A SQL Script for: Configuring CLR, Loading the assembly and Creating the Stored Procedure
Let's have a look at the vb source:
Import the appropriate namespaces
We'll call this SQLCLREmail - this will be very important later on.
Public Class SQLCLREmail
No surprises here, just setting up a basic sub procedure, only we are accepting arguments that correspond as stored procedure variables...
Public Shared Sub SendEmail(ByVal recipients As String _
, ByVal CC as String _
, ByVal BCC as String _
, ByVal subject As String _
, ByVal from As String _
, ByVal body As String _
, ByVal strAttachments as String _
, ByVal strSMTPServer as String _
, ByVal strSMTPServerPort as String _
, ByVal strSMTPServerUser as String _
, ByVal strSMTPServerPwd as String)
Using MailMsg As New MailMessage()
MailMsg.From = New MailAddress(from)
MailMsg.Subject = subject
MailMsg.Body = body
MailMsg.IsBodyHtml = True
Gotcha number one, two, three and four; in order to send to multiple mail recipients (CC, BCC and attachments too),
they have to be passed in as one long string and then looped through and added individually
If Not recipients.Equals(String.Empty) Then
Dim strRecip As String
Dim strTo() As String = recipients.Split(";")
For Each strRecip In strTo
If Not CC.Equals(String.Empty) Then
Dim strCCRecip As String
Dim strCCTo() As String = CC.Split(";")
For Each strCCRecip In strCCTo
If Not BCC.Equals(String.Empty) Then
Dim strBCCRecip As String
Dim strBCCTo() As String = BCC.Split(";")
For Each strBCCRecip In strBCCTo
If Not strAttachments.Equals(String.Empty) Then
Dim strFile As String
Dim strAttach() As String = strAttachments.Split(";")
For Each strFile In strAttach
I added this purely for my own convenience, but it certainly enhances the assembly. If a custom SMTP server is passed, this will also check for custom credentials and a custom port. Otherwise it will assume that the local server is also the SMTP server using port 25.
If Not strSMTPServer.Equals(String.Empty) Then
Dim smtp As New System.Net.Mail.SmtpClient
If Not strSMTPServerUser.Equals(String.Empty) Then
.UseDefaultCredentials = False
.Credentials = New System.Net.NetworkCredential(strSMTPServerUser, strSMTPServerPwd)
.Host = strSMTPServer
.Port = strSMTPServerPort
Dim smtp As New System.Net.Mail.SmtpClient
.Host = "localhost"
.Port = 25
That's it. Now all we have to do is compile it for use in SQL. To do that, open a command prompt (Start -> Run -> CMD) and type the following:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\SQL_CLR\SendEmail.vb
Obviously, this snippet is making some assumptions, so change the paths accordingly for your system. After that, voila! In the same directory as the VB source file, there will be a newly minted DLL, ready to be loaded into SQL.
The second part of this is to deploy the DLL to the SQL Server environment and load it as an assembly, thereby making it available for our stored procedure. In order to do that, the first step is to configure CLR in SQL:
-- In the event you want to see all of the various
-- advanced options that can be set, just execute
-- sp_configure *after* 'show advanced options' has
-- been set to 1
exec sp_configure 'show advanced options',1
-- This simply enables CLR in the SQL environment
exec sp_configure 'clr enabled',1
-- If this is to be executed on a USER database,
-- run this statement
alter database YourDatabase set trustworthy on
-- In the event this is a USER database and is
-- owned by the user, run this to change it back to 'sa'
--ALTER AUTHORIZATION ON DATABASE::YourDatabase TO sa
-- Now we load the assembly in the specified database.
-- Make sure the path to the DLL is correct.
create assembly SendEmail from 'C:\SQL_CLR\SendEmail.dll'
with permission_set = external_access
The second script will create the stored procedure that will call the CLR proc and pass in our values:
CREATE PROCEDURE [dbo].[p_SendEMail]
WITH EXECUTE AS CALLER
EXTERNAL NAME [SendEmail].[SQLCLREmail].[SendEmail]
Pretty straightforward - notice the naming convention used for the external name. Look familiar? It is the DLL name + class name + CLR proc name. Now for calling the proc:
EXEC p_SendEMail @recipients = 'email@example.com'
, @CC = 'firstname.lastname@example.org'
, @BCC = 'email@example.com'
, @subject = 'RE: Email from SQL Express'
, @from = 'Test@SQLExpress.com'
, @body = 'This is a test email from SQL Server'
, @strAttachments = 'C:\test1.txt;C:\test2.txt'
, @strSMTPServer = 'my.smtpserver.com'
, @strSMTPServer = '25'
, @strSMTPServerUser = ''
, @strSMTPServerPwd = ''
That's it - if the SMTP information is correct, the mail should be on its way.
So in wrapping up there are additional features that you may need to build in to suit your environment. These include: Logging and Error Handing (SQL TRY/CATCH block would work well). It is also important to note that this has been successfully tested and deployed in both SQL Express 2008 and SQL Express 2008 R2; it may very well work with SQL Express 2005, but it will depend greatly upon the version of the .NET framework that is installed on the server.