Sending E-Mail from SQL 2008 Express

Sending E-Mail from SQL 2008 Express

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:

 

Enable CLR in SQL 2008

To enable clr functions to execute, run the following script:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

 

After executing that script, calling clr functions work like a charm.

  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.

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s