<< Key management services (KMS) host could not be located ... | Home | Ambition IT sponsort SQL Zaterdag >>

SQL Server 2008 R2 Express: Send email with CLR

Database Mail

In the Express edition of SQL Server the Database Mail functionality does not exist. Therefor you need an alternative way to send emails with T-SQL.

Xp_smtp_sendmail

One solution is xp_smtp_sendmail. This solution has been around for ages and works very well.

CLR

Another solution is to use a CLR stored procedure. I found this solution in Greg Robidoux's tip. I have adjusted the code a bit to get it right for my requirements.

You have to follow the following steps:
  1. Write the code. In this solution it is VB.Net.
  2. Compile the code into a DLL.
  3. Create an assembly and a stored procedure.
  4. Test the solution.

1. CLR - Write the code

Copy the code below and save it into a file C:\SendMail.vb.

Imports System.Net
Imports System.Net.Mail


Public Class StoredProcedure
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spSendMail(ByVal recipients As String, ByVal subject As String, ByVal from As String, ByVal body As String, ByVal server As String, ByVal email As String, ByVal password As String)
        Dim mySmtpClient As SmtpClient

        Using myMessage As New MailMessage(from, recipients)

            myMessage.Subject = subject
            myMessage.Body = body
            myMessage.IsBodyHtml = True

            mySmtpClient = New SmtpClient(server)
            mySmtpClient.Credentials = New NetworkCredential(email, password)
            mySmtpClient.Send(myMessage)

        End Using
    End Sub
End Class

This code takes seven input parameters:

  • recipients - list of people to receive the email
  • subject - subject line of the email
  • from - who the email is from
  • body - the body of the email
  • server- the name of your smtp server (mail.server.com)
  • email - the login name or email address for your smtp server (email@yourdomain.com)
  • password - the password for your login name at the smtp server
The last two parameters can be omitted if you hard-code the values in the appropriate places, like Greg did.

2. CLR - Compile the code into a DLL

Then you compile the code. You can do that in a command prompt with the vbc.exe application which is part of the .Net 2.0 framework. You can find it in the C:\Windows\Microsoft.NET\Framework64\v2.0.50727 directory.

32-bit: On 32-bit machines the directory is c:\Windows\Microsoft.NET\Framework\v2.0.50727.

The command then is: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\vbc.exe /target:library C:\SendMail.vb.

The result is a DLL called C:\SendMail.dll.

3. CLR - Create an assembly and a stored procedure

The DLL is now ready to be used inside SQL Server. To create the assembly and the stored procedure run the following code.

Use MSDB
CREATE ASSEMBLY SendEmail FROM 'C:\SendMail.dll'
WITH PERMISSION_SET = UNSAFE 
GO

CREATE PROCEDURE [dbo].[spSendMail]
   @recipients [nvarchar](4000),
   @subject [nvarchar](4000),
   @from [nvarchar](4000),
   @body [nvarchar](max),
   @server nvarchar(4000),
   @email nvarchar(4000),
   @password nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SendEmail].[StoredProcedure].[spSendMail]

4. CLR - Test the solution

You can now send email with the code below.

Notes:

  1. You can specify multiple recipients, seperated by commas: 'email@domain.com,another@domain.com'.
  2. The body text can be HTML formatted.
  3. If you can use anonymous access to your smtp server, you can leave the @email en @password paramters empty: ''

EXEC msdb.dbo.spSendMail
@recipients = 'email@domain.com',
@subject = 'Email sent with CLR',
@from = '""The name you want to be displayed""<youremail@yourdomain.com>',
@body = 'The body can be a HTML text',
@server = 'mail.someserver.com',
@email = 'username or email',
@password = 'password'

Cleaning up
You can delete this solution by following these steps:
  1. On your computer:
    1. Delete the DLL.
    2. Delete the VB file.
  2. On SQL Server (see code below):
    1. Delete the stored procedure.
    2. Delete the assembly.
USE msdb
GO
DROP PROCEDURE dbo.spSendMail
GO
DROP ASSEMBLY SendEmail
GO

Conclusion

Figuring out how to use CLR for sending emails showed me that it is not difficult to use CLR. You can easily extend this solution in many ways to support your needs.

posted @ Friday, January 14, 2011 6:11 PM

Print