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

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
      COM / ActiveX Components
      Forms
      General Topics
      Date/Time Routines
      Email Scripts & Info
      Files/Directories & FSO
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
classicasp.aspfaq.com

ASP FAQ Tutorials :: Classic ASP 1.0 :: Email Scripts & Info :: How do I send e-mail from SQL Server?


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.

Related Articles

Can 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?

 

 


Created: 9/9/2002 | Last Updated: 6/22/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (348)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...