sending mail without SQL Mail

SQL Mail is not a very comfortable tool.

A few months ago I spent some time strugling to configure it properly and then I started to look for another solution.

That's how I discovered blat.exe utility http://www.blat.net).

Since then I've been using blat.exe to send e-mails from SQL Server. It is reliable and extremely easy to configure and use (but take a while to read the documentation). It can used free of charge for any purpose. The only drawback is that there has to be a user with with rights allowing him to execute xp_cmdshell extended procedure - this has to be considered thoroughly and carefully.

Below is a stored procedure that I am using:

/*****************************************************************
* A script sending e-mails using blat.exe utility
*
*
* EXEC [dbo].[SvSendMail] 'foo@bar.pl',
* 'SvSendMail test',
* 'This is a test'
*
* ver 1.0 (2004.12.07)
*****************************************************************/

CREATE PROCEDURE [dbo].[SvSendMail]
@to varchar(255),
@subject varchar(100) = '[no subject]',
@body varchar(1000) = '[no body]'
AS
DECLARE @command as varchar(1500)

SET @command = ''
SET @command = @command + 'Crogra~1Blatlat.exe'
SET @command = @command + ' -'
SET @command = @command + ' -to ' + '"' + @to + '"'
SET @command = @command + ' -subject ' + '"' + @subject + '"'
SET @command = @command + ' -body ' + '"' + @body + '"'

EXEC master.dbo.xp_cmdshell @command
GO
Happy mailing!

--
Marek Grzenkowicz

Thanks Chopeen.
BTW in the process of looking for information I found this one. It is worth taking a look at!

http://www.sqlservercentral.com/columnists/dharris/sendingsqlnotificationswithcdosys.asp

SSP also has this article:http://www.sql-server-performance.com/dh_sending_sql_notifications_CDOSYS.asp

--
Marek Grzenkowicz

If you want to know whether you e-mail has been sent successfully or not, check the blat return code.

Try something like this:

declare @foo varchar(100)
exec @foo = xp_cmdshell 'c:latlat.exe'
select @foo
Blat return codes are here.

--
Marek Grzenkowicz

Hi,

I found another component which can be used for sending mails from SQL Serevr. The details can be found athttp://www.sqldev.net/xp/xpsmtp.htm

--Amit