How do I send e-mail from SQL Server?

SQL Server has the ability to send mail without being triggered from ASP. So, if you have an application that needs to e-mail users once a month, or on their birthday, or to send you details every day about what went on in the database that day, you can set up a job that wakes up and sends e-mail. Similarly, you can call extended stored procedures to send mail on demand (e.g. every time a stored procedure fires).

First off, make sure your SQL Server Mail account is set up correctly - see the following articles:

Configuring and Managing SQL Mail

An Introduction to SQL Mail and SQLAgent...

KB #870675 How to send and receive e-mail with the SQL Mail feature in SQL Server 2000

KB #153159 HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server

KB #263556 INF: How to Configure SQL Mail

KB #274330 FIX: Sending Open Files as Attachment in SQL Mail Fails

KB #279867 FIX: Sending Maintenance Report by E-mail to Operator Fails

KB #281293 FIX: SQL Mail 2000 Needs Microsoft Outlook 2000 Client

KB #293422 PRB: xp_sendmail Using a Microsoft Exchange 2000 Mailbox Fails

KB #311231 INF: Frequently Asked Questions - SQL Server - SQL Mail

KB #315886 INF: Common SQL Mail Problems

KB #321183 HOW TO: Troubleshoot Your SQL Mail Issue

KB #811035 PRB: The xp_sendmail Stored Procedure Fails

KB #820220 PRB: 0x80070005 or 0x80040005 Error Occurs When You Send E-Mail

KB #833045 The xp_sendmail extended stored procedure does not run [...]

(For plenty of information on using XP_SendMail, see http://tinyurl.com/1d29.)

If you can not or will not configure SQL Mail, or you are frustrated with getting it to work, you can use CDO (see KB #312839) or Gert Draper's XP_SMTP_SendMail (from XPSMTP). I prefer the XP_SMTP_SendMail procedure over both CDO and SQL Mail. XP_SMTP_SendMail is an extended stored procedure that will send mail through any valid SMTP server you specify, and supports HTML formatting. This makes sending mail very flexible, especially if your SQL Server is not a part of a domain, or you're not running Exchange - and you don't have or don't want to install CDO on your database server(s).

To fire off e-mail on demand

Let's say you want to send an e-mail from within a stored procedure called foo. You can do it like this:

With XP_SendMail:

CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other actions
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired '+
CONVERT(VARCHAR, GETDATE())

EXEC master..xp_sendmail
@recipients='you@you.com',
@message = @body,
@subject = 'foo was fired.'
END

-- or you can do it conditionally:

CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other action
IF @@ROWCOUNT > 0
BEGIN
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired ' +
CONVERT(VARCHAR, GETDATE()) +
CHAR(13) + CHAR(10) +
CONVERT(VARCHAR, @@ROWCOUNT)

EXEC master..xp_sendmail
@recipients='you@you.com',
@message = @body,
@subject = 'foo was fired.'
END
END

With XP_SMTP_SendMail (which assumes you are running SQL Server 7.0 or higher, have installed XP_SMTP_SendMail, and have a valid and properly configured SMTP Server):

CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other actions
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired '+
CONVERT(VARCHAR, GETDATE())

EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@from = 'someone@somewhere.com',
@message = @body,
@subject = 'foo was fired.',
@server = 'smtp.yourdomain.com'
END

-- or you can do it conditionally:

CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other action
IF @@ROWCOUNT > 0
BEGIN
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired ' +
CONVERT(VARCHAR, GETDATE()) +
CHAR(13) + CHAR(10) +
CONVERT(VARCHAR, @@ROWCOUNT)

EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@from = 'someone@somewhere.com',
@message = @body,
@subject = 'foo was fired.',
@server = 'smtp.yourdomain.com'
END
END

If you need to send an attachment, you can simply add the following parameter:

@attachments = 'c:\attachment.txt'

CDO.Message

This sample assumes you are running Windows 2000 or higher, have a user that has access to sp_OA* procedures, and have a valid and properly configured SMTP server:

CREATE PROCEDURE dbo.sendMail_With_CDOMessage
@to VARCHAR(64),
@subject VARCHAR(255),
@body VARCHAR(1024)
AS
BEGIN
SET NOCOUNT ON

DECLARE
@handle INT,
@return INT,
@s VARCHAR(64),
@sc VARCHAR(1024),
@up CHAR(27),
@from VARCHAR(64),
@server VARCHAR(255),
@filename VARCHAR(255)

SET @s = '"http://schemas.microsoft.com/cdo/configuration/'

SELECT
@s = 'Configuration.Fields(' + @s,
@up = 'Configuration.Fields.Update',
@from = 'someone@somewhere.com',
@server = 'smtp.yourdomain.com'
-- or IP address, e.g. '127.0.0.1'

-- if you want an attachment:
,@filename = 'C:\folder\file.ext'

EXEC @return = sp_OACreate 'CDO.Message', @handle OUT
SET @sc = @s + 'sendusing").Value'
EXEC @return = sp_OASetProperty @handle, @sc, '2'
SET @sc = @s + 'smtpserver").Value'
EXEC @return = sp_OASetProperty @handle, @sc, @server
EXEC @return = sp_OAMethod @handle, @up, NULL
EXEC @return = sp_OASetProperty @handle, 'To', @to
EXEC @return = sp_OASetProperty @handle, 'From', @from
EXEC @return = sp_OASetProperty @handle, 'Subject', @subject
EXEC @return = sp_OASetProperty @handle, 'TextBody', @body

IF @filename IS NOT NULL
EXEC @return = sp_OAMethod @handle, 'AddAttachment', NULL, @filename

EXEC @return = sp_OAMethod @handle, 'Send', NULL
IF @return <> 0
BEGIN
PRINT 'Mail failed.'
IF @from IS NULL
PRINT 'From address undefined.'
ELSE
PRINT 'Check that server is valid.'
ELSE
PRINT 'Mail sent.'

EXEC @return = sp_OADestroy @handle
END
GO

You can also use the component ASPEmail (from www.aspemail.com) in this way. There is a code sample at support.persists.com.

To schedule mail

Now, let's say you have a stored procedure that checks a table for birthdays, and e-mails a happy birthday to all matches. It might look like this (this assumes XP_SMTP_SendMail is the method you're using):

CREATE TABLE dbo.birthdays
(
fullname VARCHAR(128),
email VARCHAR(128),
birthdate SMALLDATETIME
)
GO

INSERT birthdays VALUES('AB', 'ab@nonexists.com', '19740201')
INSERT birthdays VALUES('CB', 'cb@nonexists.com', '19870123')
INSERT birthdays VALUES('TC', 'tc@nonexists.com', '20011227')
GO

-- and include your own address, with today's date, for testing!

CREATE PROCEDURE dbo.proc_happyBirthday
AS
BEGIN
SET NOCOUNT ON

-- if you have more than 80 matches on any
-- possible day, you may want to use a cursor
-- instead - this will only handle 8000 chars.

DECLARE @BCCList VARCHAR(8000)
SET @BCCList = ''

-- concat the BCC list for people with birthdays
-- of today. Note that this job won't run on
-- February 29th of non-leap years, so you may
-- want to add logic for that...

SELECT @BCCList = @BCCList + ';' + COALESCE(email, '')
FROM Birthdays
WHERE
MONTH(birthdate) = MONTH(GETDATE())
AND DAY(birthdate) = DAY(GETDATE())

-- trim off leading ';' character from concatenation

SET @BCCList = SUBSTRING(@BCCList, 2, LEN(@BCCList))

EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@BCC = @BCCList,
@from = 'someone@somewhere.com',
@subject = 'Happy Birthday to you!',
@message = 'You belong in a zoo!',
@server = 'smtp.yourdomain.com'
END
GO

Currently, you might be invoking this stored procedure manually, or having a scheduled ASP / VBS script execute the stored procedure through ADO. Wouldn't it be nice to have the database take care of this, without involving other machines and code?

Make sure SQL Server agent is running. In Enterprise Manager, expand the server in question, and expand the Management node. Make sure SQL Server Agent is started.

If it is not running, right-click it and hit Start. Right-click and hit properties, and on the advanced tab, make sure SQL Server Agent is set to Auto-restart if that option is available:

Next, you will need to create a job. Under SQL Server agent, right-click Jobs and choose 'New Job...' You will see the following dialog:

On the General tab, name the job (e.g. SendBirthdayNotices).

On the Steps tab, click New... and enter 'Step 1' into the Step Name: box. Switch the database dropdown to the database where your stored procedure lives, make sure 'Transact-SQL' is selected, and enter 'EXEC proc_happyBirthday'.

On the Schedules tab, click New Schedule... and enter 'Schedule 1' in the Name: box. Make sure 'enabled' is checked, and click the 'Change...' button. Set up a schedule that makes sense... e.g. daily at noon or daily at 5:00 am. The interface may look a bit daunting but it's pretty simple to configure.

Now, you can monitor your job's progress in the jobs view to see last/next run dates, and right-click the job itself to see the job history. You can also right-click the job and start it now (e.g. if you created it AFTER the scheduled run time but still want it to run today).

Job failure notifications

When moving away from SQL Mail, one thing that people have a problem with is losing their ability to send notifications when jobs fail. The current options require SQL Mail to be installed, but is there a way around it? Of course there is.

Right-click the job in question, and hit Properties. Create an additional step in the job (by clicking the New button), and call this step "E-mail on failure." Make sure it is the *last* step. On the general tab of the step's properties, select the correct database, and then enter the following for the command:

EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@from = 'someone@somewhere.com',
@subject = '<job name> failed.',
@server = 'smtp.yourdomain.com'

Of course, you can add @message and other parameters if you wish.

On the advanced tab of the step's properties, set both the on success and on failure actions to "Quit the job reporting failure." As you'll see in a minute, this step will only ever be fired if a previous step fails, and you probably want to maintain other tracking (such as the Windows event log) when the job fails. Hit Apply and OK.

Now, here's where the magic happens. Go to the previous step, and select the Edit button. On the Advanced tab, change the on failure action to "Go to the next step." (You can choose go to step n, but this might cause order/dependency problems when scripting the job.) Make sure the on success action is set to "Quit the job reporting success." Hit Apply and OK. You may be asked if you're really sure this is the way you want it set up. Feel free to double-check before committing.

You can get more complicated, for example if a job has many steps, each step can be set to fail over to a different "e-mail on failure" step.

Alerts

One element that XP_SMTP_SendMail does not cover is SQL Agent alerts. People have gone the SMTP route, in an effort to avoid Outlook / Exchange / MAPI altogether, only to find that they still need to implement those technologies in order to receive alerts via e-mail. Well, not any more. A colleague has published a free SMTP Alerter tool that works alongside XP_SMTP_SendMail to generate alerts and send them via SMTP.

Please let us know if you have any questions or comments about this article.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I send e-mail from SQL Server?

Related Articles