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:

1. The source VB file.
2. 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

Imports System.Net 
Imports System.Net.Mail

We'll call this SQLCLREmail - this will be very important later on.

Public Class SQLCLREmail 
 <Microsoft.SqlServer.Server.SqlProcedure()> _

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 
               MailMsg.To.Add(New MailAddress(strRecip)) 
           Next 
        End If
        If Not CC.Equals(String.Empty) Then 
           Dim strCCRecip As String 
           Dim strCCTo() As String = CC.Split(";") 
           For Each strCCRecip In strCCTo 
               MailMsg.CC.Add(New MailAddress(strCCRecip)) 
           Next 
        End If
        If Not BCC.Equals(String.Empty) Then 
           Dim strBCCRecip As String 
           Dim strBCCTo() As String = BCC.Split(";") 
           For Each strBCCRecip In strBCCTo 
               MailMsg.BCC.Add(New MailAddress(strBCCRecip)) 
           Next 
         End If
        If Not strAttachments.Equals(String.Empty) Then 
           Dim strFile As String 
           Dim strAttach() As String = strAttachments.Split(";") 
           For Each strFile In strAttach 
               MailMsg.Attachments.Add(New Net.Mail.Attachment(strFile.Trim())) 
           Next 
        End If

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 
           With smtp 
              If Not strSMTPServerUser.Equals(String.Empty) Then
                .UseDefaultCredentials = False 
                .Credentials = New System.Net.NetworkCredential(strSMTPServerUser, strSMTPServerPwd)
              End If
                .Host = strSMTPServer 
                .Port = strSMTPServerPort 
                .Send(MailMsg) 
           End With 
        Else
           Dim smtp As New System.Net.Mail.SmtpClient
           With smtp
                .Host = "localhost"
                .Port = 25 
                .Send(MailMsg) 
           End With
        End If
      End Using 
    End Sub 
End Class

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
reconfigure
GO
-- This simply enables CLR in the SQL environment
exec sp_configure 'clr enabled',1
reconfigure
go
-- If this is to be executed on a USER database, 
-- run this statement
alter database YourDatabase set trustworthy on
go
-- 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
go
use YourDatabase
go
-- 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
GO

The second script will create the stored procedure that will call the CLR proc and pass in our values:

CREATE PROCEDURE [dbo].[p_SendEMail] 
 @recipients [nvarchar](4000), 
 @CC [nvarchar](4000),
 @BCC [nvarchar](4000),
 @subject [nvarchar](4000), 
 @from [nvarchar](4000), 
 @body [nvarchar](4000),
 @strAttachments [nvarchar](4000),
 @strSMTPServer [nvarchar](300), 
 @strSMTPServerPort [nvarchar](10),
 @strSMTPServerUser [nvarchar](300), 
 @strSMTPServerPwd [nvarchar](300)
WITH EXECUTE AS CALLER 
AS 
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 = 'user1@company.com'
 , @CC = 'user2@company.com'
 , @BCC = 'user3@company.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.