Monday, December 31, 2007 9:58 AM tonyrogerson

CLR Stored Procedure to utilise more of the MailMessage .NET class rather than DB Mail.

Running a user group and being one of the founders of SQLBits I have a need to send bulk emails out to a few thousand people; I also do this for some of my clients. Using Database Mail in SQL Server 2005 works fine but has the disadvantage that only the basic email properties are exposed, so, you can't set the Sender property etc...

Anyway, the CLR below does just that; it basically means that instead of the mail looking like SPAM to a few mail servers you now get the more friendly headers shown in Outlook and hotmail -> From: tonyrogerson@torver.net on behalf of UK SQL Server User Group (sqlfaq@sqlserverfaq.com).

Unfortunetly the assembly needs to run with the 'External' permission level which means you'll have to set the database to TRUSTWORTHY ON etc...

I've only coded it for one attachment, but I'll probably expand that, the parameter AttachmentFileName would simply be CSV which I'd split out in the CLR proc. The same with the ToEmail.

This procedure will also be handy for those people who use SQL Server Express edition because Database Mail doesn't work on that edition.

The example call in SQL Server T-SQL would be:

declare @SendStatus varchar(max)

declare @rc int

 

exec @rc = smtpfullemail

              @SMTPServer = 'yoursmtpservername',

              @SenderEmail = 'tonyrogerson@torver.net',

              @FromEmail = 'sqlfaq@sqlserverfaq.com',

              @FromName = 'UK SQL User Group',

              @ToEmail = 'amember@hotmail.com',

              @ReplyTo = 'sqlfaq@sqlserverfaq.com',

              @Subject = 'Test email',

              @Body = '<b>Hello Tony</b>',

              @AttachmentFileName = 'c:\somefile.txt',

              @SendStatus = @SendStatus OUTPUT

 

 

select @rc, @SendStatus

Here is the CLR code in VB.NET:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Runtime.InteropServices

Imports System.Net.Mail

 

 

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Function SMTPFullEmail(ByVal SMTPServer As SqlString, _

                                         ByVal SenderEmail As SqlString, _

                                         ByVal FromEmail As SqlString, _

                                         ByVal FromName As SqlString, _

                                         ByVal ToEmail As SqlString, _

                                         ByVal ReplyTo As SqlString, _

                                         ByVal Subject As SqlString, _

                                         <SqlFacet(MaxSize:=-1)> ByVal Body As SqlString, _

                                         ByVal AttachmentFileName As SqlString, _

                                         <Out()> ByRef SendStatus As SqlString) As SqlInt32

 

        Dim Message As New MailMessage

 

        '   Set up the message

        Message.Sender = New MailAddress(SenderEmail.ToString)

        Message.From = New MailAddress(FromEmail.ToString, FromName.ToString)

 

        Message.To.Add(ToEmail.ToString)

        Message.Subject = Subject.ToString

        Message.Body = Body.ToString

        Message.IsBodyHtml = True

        If AttachmentFileName.ToString <> "" Then Message.Attachments.Add(New Attachment(AttachmentFileName.ToString))

 

        Dim smtp As New SmtpClient

 

        '   Send it

        smtp.Host = SMTPServer.ToString

 

        Try

            smtp.Send(Message)

 

            SendStatus = CType("OK", SqlString)

            SMTPFullEmail = 0

 

        Catch ex As Exception

            SendStatus = CType(ex.Message, SqlString)

            SMTPFullEmail = 1

 

        End Try

 

    End Function

 

End Class

[Updated 5th Feb 2008] Note the above Body etc.. are limited to 4000 bytes, to make it varchar(max) modify thus... 

<SqlFacet(MaxSize:=-1)> ByVal Body As SqlString 

 

Filed under:

Comments

# Format query output into an HTML table - the easy way

Friday, October 24, 2008 9:20 AM by Tony Rogerson's ramblings on SQL Server

Ok, I wrote a big stored procedure last time my entry on &quot; send table or view as embedded html &quot;;