Sending E-Mail from SQL 2008 Express
November 5, 2010 § Leave a comment
Sending E-Mail from SQL 2008 Express
By Andrew Prisk, 2010/11/04
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
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 = '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.