Keith Rull - Sending e-mails in SQL Server using XPSMTP

Friday, June 10, 2005

Sending e-mails in SQL Server using XPSMTP

XPSMTP is the easiest and fastest way to send e-mails in SQL Server. Its is so easy to use. All you need to do is download the DLL to the BINN directory of your SQL Server. Register the XP stored procedure and Grant permission to the stored procedure. XSMTP even supports MSDE. Best of all, IT'S ABSOLUTELY FREE!

Here's a brief description taken from the XPSMTP website:

"XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.

It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds)."

I have used XMPTP once when i was asked to create a dts package that would be scheduled every end of the day to send an attendance summary. The stored procedure that i used for that project is listed below:

ALTER PROCEDURE spSendAttendanceSummary
AS

SET NOCOUNT ON

-- declare all variables!

DECLARE    @UserName    varchar(20),
    @LoginTime    varchar(20),
    @LogoutTime    varchar(20),
    @From        varchar(50),
    @To        varchar(50),
    @Subject    varchar(255),
    @Body1        varchar(8000),
    @BodyHeader    varchar(100),
    @BodyFooter    varchar(100),
    @Header        varchar(200)

    SET @Body1 = ''

    SET @Header = '<TR>'
    SET @Header = @Header + '<TD>UserName</TD>'
    SET @Header = @Header + '<TD>LoginTime</TD>'
    SET @Header = @Header + '<TD>LogoutTime</TD>'
    SET @Header = @Header + '</TR>' + Char(10)

-- declare the cursor
DECLARE BodyData CURSOR FOR
    SELECT UserName, LoginTime, LogoutTime
    ORDER BY UserName, LoginTime, LogoutTime

OPEN BodyData

FETCH BodyData INTO     @UserName,
            @LoginTime,
            @LogoutTime

-- start the main processing loop.
WHILE @@Fetch_Status = 0

BEGIN
    DECLARE @Body        Varchar(400)

    SET @Body = '<TR>'
    SET @Body = @Body + '<TD>'+ @UserName + '</TD>'
    SET @Body = @Body + '<TD>'+ @LoginTime + '</TD>'
    SET @Body = @Body + '<TD>'+ @LogoutTime + '</TD>'
    SET @Body = @Body + '</TR>' + Char(10)

    SET @Body1 = @Body1 + @Body


    FETCH BodyData INTO     @UserName,
                @LoginTime,
                @LogoutTime

END

CLOSE BodyData

DEALLOCATE BodyData

IF @Body1 <> ''
    BEGIN

    SELECT @BodyHeader = '<HTML><HEAD></HEAD><BODY><H1>Attendance Summary</H1><TABLE BORDER=1>'
    SELECT @BodyFooter = '</TABLE></BODY></HTML>'

    SELECT @Body1 = @BodyHeader + @Header + @Body1 + @BodyFooter


    EXEC master.dbo.xp_smtp_sendmail
        @FROM            = N'whoever@whatsoever.com',
        @TO            = N'whoelse@whatsoever.com',
        @server         = N'your.smtp.server',
        @subject        = N'your subect!',
        @type         = N'text/html',
        @message        = @Body1

    END
RETURN

So download now! it's a must have!

Friday, June 10, 2005 8:49:40 PM (GMT Standard Time, UTC+00:00)

Comments [0] -
SQL | Tech News and Issues

Comments are closed.