December 2007 - Posts

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 

 

Are you trying to count how many words there are in a string? Well, first I looked at the LEN and REPLACE ( ... ', ' ', '' ) method but you soon realise it doesn't work - you start counting double spaces as actual words.

Here is a function that gets round the problem; it also excludes non word stuff like a question mark.

create function fn_how_many_words(
    @source varchar(max) )

    returns int

as

begin

    declare @start int

 

    --  break any multiple spaces down

    while 1=1

    begin

        set @start = len( replace( @source, '  ', ' ' ) )

        set @source = replace( @source, '  ', ' ' )

 

        if @start = len( replace( @source, '  ', ' ' ) )

            break

 

    end

 

    --  get rid of any non letters and numbers

    set @start = 1

    while @start <= 255

    begin

        if @start not between ascii( 'a' ) and ascii( 'z' )

           and @start not between ascii( 'A' ) and ascii( 'Z' )

           and @start not between ascii( '0' ) and ascii( '9' )

           and @start <> ascii( ' ' )

 

            set @source = replace( @source, char( @start ), '' )

 

        set @start = @start + 1

 

    end

   

    return( len( @source ) - len( replace( @source, ' ', '' ) ) + 1 )

 

end

 

Back to more advanced basics if that is not too much a contradiction in terms.

I'm thinking around a session that will cover some really cool but not very well understand features within SQL Server, first taking the time to explain at the basic level what they are and how they work and then steadily building into uses and advanced techniques, that is:

Derived Tables: what are they, how can they be used, comparison to CTE and temporary tables, logic bombs, nesting.

Case: when and how to use, how it's structured, streamlining aggregations using CASE.

Is there anything I'm missing that would complement these features?

Tony.

Yes, yes, I'll get back to posting technical content over christmas; work has been very mad of late - lots to do, interesting stuff but lot's to do.

I've a back log of stuff I want to blog about - if you've got any particular topics you think need explaining then drop me an email.

Anyway, meet Rosie.... keeps me sane when working from home, well, her and Radio 2 ;)

Tony.

 

Call for speakers – SQL Bits 2008, 1st March – Birmingham

The October SQL Bits was a resounding success, we had 321 delegates on the day and feedback was brilliant, hot on the success of that conference we have started the planning for the next SQL Bits conference which will be held at the Lakeside Conference Centre in Birmingham on 1st March 2008.

We are looking for sessions that cover any topic related to SQL Server, Database Administration like Design, High Availability, Tuning etc.., Development like  coding (application, CLR etc..), Business Intelligence – SSAS, SSIS, SSRS etc... – Basically anything you think relevant and would interest the general SQL professional using SQL Server whether full or part time.

Please submit your sessions to http://www.sqlbits.com/information/sessionsubmission.aspx, any problems or questions please email me tonyrogerson@torver.net and I’ll do my best to answer.

Sessions are 60 minutes; although we can split the sessions up into multiples if you feel you only want to do part of that time.

The deadline for session submission is 25th December so get your session proposals in quickly!

Many thanks,

Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
http://sqlserverfaq.com