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_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):
If you need to send an attachment, you can simply add the following parameter:
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:
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):
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:
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.
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.
Related ArticlesCan I get CDO messages to return a read receipt?
Can I use a remote SMTP server with CDONTS.NewMail?
How do I alter the priority / importance of an e-mail message?
How do I prevent my links from wrapping in an e-mail?
How do I put carriage returns into an e-mail?
How do I send e-mail from ASP?
How do I send e-mail in HTML format?
How do I send e-mail with CDO?
How do I validate an e-mail address?
Should I use form action=mailto: to mail the results of a form?
Where can I get more details about configuring / using CDO?
Why can't ASP handle 80,000 e-mails?
Why do CDONTS messages end up in the badmail folder?
Why do I get 8000900F errors?
Why do I get 80040108 errors?
Why do I get 8004020A errors?
Why do I get 80090020 errors?
Why do I get C00402CE / C00402C7 errors?
Why does CDO.Message give 800C000D errors?
Why does CDO.Message give me 8004020F errors?
Why does CDO.Message give me 80040213 errors?
Why does CDO.Message give me 80040222 errors?
Why does my CDONTS mail hang out in the queue or pickup folders?
Why is e-mail to certain domains being rejected?