SQL Server Performance

sending mail without SQL Mail

Discussion in 'Contribute Your Performance and Clustering Tips' started by chopeen, Feb 14, 2005.

  1. chopeen Member

    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]'
    DECLARE @command as varchar(1500)

    SET @command = ''
    SET @command = @command + 'C:progra~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
    Happy mailing!

    Marek Grzenkowicz
  2. satya Moderator

  3. netscriptions New Member

  4. chopeen Member

  5. chopeen Member

    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
  6. amitarora New Member

Share This Page