Sql Agent and Database mail is this what I need for email reminders?

Question

Hi

I want to use ms sql 2005 to send email reminders to my customers. I want to send daily reminders that should run around midnight(when traffic would be low).

So I think the best way to do this is through ms sql 2005 and I been looking into sql agent and database mail but I am unsure how to make a script that would dynamically get the information for each customer(each customer will have a different email sent to them).

So I need to query one table to get everyone email address, I need a query to get that customers info that I am planning to send.

Once I have this information I then need to then format the email and send them(so if I have 50 customers - 50 different customized emails will be sent out).

To: Always different person, From: static probably won't change, Title- probably will be always different, Body - Always different.

My body always will require it to be html since I will be using html tags.

So can someone give me a watered down example to get started? I am not that that great with databases and have not used ms sql 2005 very much.

I think I need sql agent as it can do it at set times, and of course database mail to send it all away.

But after that it is a big blank. I saw this post

http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

but she is using triggers so I don't know if I have to use this in combination as well.

Edit

Here is some tables I am trying to keep them simple so I can understand what is going on and probably will be different then my end result.

Table A
id - pk - incrementing int
email - varchar(20)

Table B
TableBId  - pk - incrementing int
id - fk
title - varchar(30)
body - varchar(2000)
SendOutDate - datetime
type - varchar(5)

Sample Data

Table A

Id       email
------------------
1       [email protected]
2       [email protected]
3       [email protected]


Table B
TableBId   Id   title          body          sendoutDate           type
---------------------------------------------------------------------------
1          1    Reminder1     <b>test</b>    12/24/2010 12:30 pm    Email
2          1    Reminder2     hello          12/25/2010 12:30 pm    Email
3          1    Reminder3     hi text        12/28/2010 11:30 pm    SMS
4          1    Reminder4     again          12/29/2010 5:00am      Both
5          2    Your Reminder  test          12/24/2010 2:30 am     Email
6          3    jim            bo            12/25/2010 11:59:59 pm   SMS

Ok so a couple things to note in future version I want to support sending out Email and SMS alerts so they would be in the same table just with a different type. "Both" meaning "Email and SMS" alert would be sent out.

Of course right now lets just stick with Email alerts but I wanted to show you the whole story but I am assuming these would be 2 different operations so a where clause probably will be needed.

Next the time date. I want to send out notification around midnight so midnight to midnight should be 24 hours.

So if we from December 24th 12am to December 25th 12pm all notifications in this range should be sent out then the next day would be Dec 25th to Dec 26th and so on.

I am also guessing a join would be needed to join Table A and B together. Then I would need to grab the data out and put it in some variables or something.

So how could I write this sp? KM was saying I need to loop over some stuff or something like that. I am guessing I have to write some sql type forloop then?

Answer 1

Create a SQL Server agent job that runs daily at your selected time. Have that job run a stored procedure. In that stored procedure you'll need to loop over what ever you need to send an e-mail for. One iteration per distinct e-mail, calling EXEC msdb.dbo.sp_send_dbmail ...,...,.. to send the actual e-mail. Beyond that, you need to ask specific questions. You'll need to build up a list of recipient e-mail addresses into a local variable, as well as construct a message body, but without any details in the question how can I explain what to do?

EDIT after OP's edit with more details:

Set up tables, I used @TableVariables because I don't want to create tables on my test system, you need to create regular tables, with proper PK, FKs, indexes, etc.

SET NOCOUNT ON
DECLARE @EmailContacts table (EmailID      int
                             ,EmailAddress varchar(20)
                             )
INSERT @EmailContacts VALUES (1,'[email protected]')
INSERT @EmailContacts VALUES (2,'[email protected]')
INSERT @EmailContacts VALUES (3,'[email protected]')

DECLARE @EmailMessages table (MessageId     int
                             ,MessageType   char(1)  --FK to EmailMessageType.MessageType
                             ,EmailID       int          --FK to EmailContacts.EmailID
                             ,SendOutDate   datetime
                             ,MessageTitle  varchar(30)
                             ,MessageBody   varchar(2000)
                             )
INSERT @EmailMessages VALUES(1,'E', 1,'12/24/2010 12:30 pm'   , 'Reminder1'     ,'<b>test</b>')
INSERT @EmailMessages VALUES(2,'E', 1,'12/24/2010 12:30 pm'   , 'Reminder2'     ,'hello'      ) --<<changed date so there would be multiple to loop over
INSERT @EmailMessages VALUES(3,'S', 1,'12/28/2010 11:30 pm'   , 'Reminder3'     ,'hi text'    )
INSERT @EmailMessages VALUES(4,'B', 1,'12/29/2010 5:00 am'    , 'Reminder4'     ,'again'      )
INSERT @EmailMessages VALUES(5,'E', 2,'12/24/2010 2:30 am'    , 'Your Reminder' ,'test'       )
INSERT @EmailMessages VALUES(6,'S', 3,'12/25/2010 11:59:59 pm', 'jim'           ,'bo'         )

DECLARE @EmailMessageTypes table (MessageType           char(1)
                                 ,MessageTpeDescription varchar(30)
                                 )
INSERT @EmailMessageTypes VALUES ('E','Email')
INSERT @EmailMessageTypes VALUES ('S','SMS')
INSERT @EmailMessageTypes VALUES ('B','Both')
SET NOCOUNT OFF

this is what goes in the stored procedure

--inside the stored procedure
BEGIN TRY

    DECLARE @RunDate        datetime
           ,@ReturnValueX   int
           ,@ErrorMsg       varchar(5000)
           ,@Rows           int

    SET @RunDate='12/24/2010 12:30 pm'  --GETDATE() --<<use GETDATE() I used '12/24... so it would find the test data

    --area to process current row from loop
    DECLARE @Process_MessageId              int
           ,@Process_MessageType            char(1)
           ,@Process_MessageTpeDescription  varchar(30)
           ,@Process_EmailID                int     
           ,@Process_EmailAddress           varchar(20)     
           ,@Process_SendOutDate            datetime
           ,@Process_MessageTitle           varchar(30)
           ,@Process_MessageBody            varchar(2000)

    SET @Process_MessageId=0

    WHILE ISNULL(@Process_MessageId,-1)>=0
    BEGIN
        --get the next row to process
        SELECT
            @Process_MessageId                  =m.MessageId
                ,@Process_MessageType           =m.MessageType 
                ,@Process_MessageTpeDescription =t.MessageTpeDescription
                ,@Process_EmailID               =m.EmailID    
                ,@Process_EmailAddress          =c.EmailAddress 
                ,@Process_SendOutDate           =m.SendOutDate 
                ,@Process_MessageTitle          =m.MessageTitle
                ,@Process_MessageBody           =m.MessageBody 
            FROM @EmailMessages      m
                INNER JOIN (SELECT
                                MIN(mm.MessageId) AS MinMessageId
                                FROM @EmailMessages  mm
                                WHERE mm.MessageId>@Process_MessageId AND mm.SendOutDate>=@RunDate AND mm.SendOutDate<=DATEADD(hour,1,@RunDate)
                           ) dt ON m.MessageId=MinMessageId
                LEFT OUTER JOIN @EmailMessageTypes t ON m.MessageType=t.MessageType
                LEFT OUTER JOIN @EmailContacts     c ON m.EmailID=c.EmailID
        SELECT @Rows=@@ROWCOUNT

        IF @Rows=0
        BEGIN
            BREAK --no more rows found
        END

        --process the row
        --comment out the PRINT when it is in production, it is nice have when running it from SQL Server Management Studio, but not necessary when run from a job
        PRINT 'Sending mail, TO: '+ISNULL(@Process_EmailAddress,'null')+', SUBJECT: '+ISNULL(@Process_MessageTitle,'null')+', BODY: '+ISNULL(@Process_MessageBody,'null')
        EXECUTE @ReturnValueX = msdb.dbo.sp_send_dbmail
                                    @recipients            =@Process_EmailAddress
                                   ,@body                  =@Process_MessageBody
                                   ,@body_format           ='HTML'
                                   ,@subject               =@Process_MessageTitle
                                   ,@profile_name          ='YourEmailProfile'

        IF @ReturnValueX!=0
        BEGIN
            SET @ErrorMsg='Error '+ISNULL(CONVERT(varchar(30),@ReturnValueX),'unknown')+', calling msdb.dbo.sp_send_dbmail '
                                 +'   @recipients='            +ISNULL(@Process_EmailAddress  ,'null')
                                 +'  ,@body='                  +ISNULL(@Process_MessageBody   ,'null')
                                 +'  ,@body_format='           +ISNULL('HTML'                 ,'null')
                                 +'  ,@subject='               +ISNULL(@Process_MessageTitle  ,'null')
                                 +'  ,@profile_name='          +ISNULL('YourEmailProfile'     ,'null')
            RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
        END --IF ERROR

    END --WHILE

END TRY
BEGIN CATCH

    --use your error logging method of choice here
    --INSERT INTO YourErrorLogTable (...,...,...) VALUES (...,...,...,'fatal error in '+ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
    --             +' error was :'
    --             +CASE WHEN ERROR_NUMBER()     IS NOT NULL THEN 'Msg '         +CONVERT(varchar(30),   ERROR_NUMBER()     ) ELSE '' END
    --             +CASE WHEN ERROR_SEVERITY()   IS NOT NULL THEN ', Level '     +CONVERT(varchar(30),   ERROR_SEVERITY()   ) ELSE '' END
    --             +CASE WHEN ERROR_STATE()      IS NOT NULL THEN ', State '     +CONVERT(varchar(30),   ERROR_STATE()      ) ELSE '' END
    --             +CASE WHEN ERROR_PROCEDURE()  IS NOT NULL THEN ', Procedure ' +                       ERROR_PROCEDURE()    ELSE '' END
    --             +CASE WHEN ERROR_LINE()       IS NOT NULL THEN ', Line '      +CONVERT(varchar(30),   ERROR_LINE()       ) ELSE '' END
    --             +CASE WHEN ERROR_MESSAGE()    IS NOT NULL THEN ', '           +                       ERROR_MESSAGE()      ELSE '' END

    --will echo back the complete original error message
    DECLARE @ErrorMessage nvarchar(4000), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    --RETURN 9999

END CATCH

OUTPUT:

Sending mail, TO: [email protected], SUBJECT: Reminder1, BODY: <b>test</b>
Sending mail, TO: [email protected], SUBJECT: Reminder2, BODY: hello