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 = '<!-- Start Table Bulid -->'
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 <> '<!-- Start Table Bulid -->'
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!
Posted
06-10-2005 8:49 PM
by
keithrull