Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm planning to use SQL Server Database Mail.

I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new receiver records added to this table.

I would like to know pros and cons from the experts who already have used, or are aware of, this Database Mail feature. Is this the correct system for sending mails, or is there better options to send mails from SQL Server?

Update: Above mentioned table is a kind of queue table for mails to be sent. There are many operations going on in the application and all these operations need to send mail once each transaction is complete. Hence, they store that information in this table. Later on, at every minute, we retrieve data from this table and send mails accordingly, marking that record as sent. I hope this better clarifies my requirements.

share|improve this question

migrated from stackoverflow.com Aug 19 '11 at 16:44

This question came from our site for professional and enthusiast programmers.

    
Why are you creating a table for this? Database Mail already implements a queueing system (using Service Broker) so that the sending code doesn't have to wait for the mail to be sent. –  Damien_The_Unbeliever Aug 19 '11 at 10:23
    
@damien I've updated my question. Please have a look at that. –  JPReddy Aug 19 '11 at 10:56
    
I'm still failing to understand what this table is giving you that isn't already implemented by Database Mail. sp_send_dbmail: "When executing sp_send_dbmail from within an existing transaction, Database Mail relies on the user to either commit or roll back any changes. It does not start an inner transaction." –  Damien_The_Unbeliever Aug 19 '11 at 10:59
    
@damien This table is also used to display the status of all the mails in future to the user along with some other data in that table. –  JPReddy Aug 19 '11 at 11:10
add comment

3 Answers 3

up vote 2 down vote accepted

It seems that the two things you are trying to accomplish are (a) a single process for sending mail and (b) the ability to check the status of sent e-mail messages later.

If this is your requirement, it is totally acceptable to have a table to which various operations write simple information about e-mail notifications which are sent. However, you do not need to store the whole message or the message status.

From the Technet entry for sp_send_dbmail:

When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message.

And, from an MSDN walkthrough titled How to: Check the Status of E-Mail Messages Sent With Database Mail (Transact-SQL):

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems views of the msdb database. The Database Mail external program logs activity and displays the log through the Windows Application Event Log and the sysmail_event_log view in the msdb database. To check the status of an e-mail message, run a query against this view. E-mail messages have one of four possible statuses: sent, unsent, retrying, and failed.

All you need to store, then, is the ID of the message which was queued, along with any application-specific information (such as which operation generated the notification, or the reason the notification was generated). When you are ready to get the status of a message, you can join back to one of the views described in the walkthrough above using the mailitem_id you saved to your table. If you do not have such application-specific data, there is no need for a custom tracking table at all, since you can search the sysmail* views by recipient.

As for the "single process for sending mail" approach, I have used that abstraction myself and I don't see anything wrong with it, though I would offer some suggestions and caveats.

  • If the reason you are polling the table every minute is that the messages are time-sensitive, this is probably not a good approach and you really should consider moving the sp_send_dbmail call back into to the operation that needs to send notifications.
  • When you call sp_send_dbmail inside a transaction, the message is never sent if the transaction is rolled back. I mention that because you said you want the e-mail to be sent only when the transactions succeed.
  • If you do need to keep some kind of "message builder" table to which various operations submit pending notifications, there is no need to keep records in this table once they have been processed. From that point forward, all the data you need should be available in the sysmail* views, combined (if necessary) with the tracking table above.

I would like to provide an example where I have used this kind of abstraction. I have an application in production for which notifications are generated several times a minute, but users only need to receive these notifications once per day (or, in some cases, a few times per day). Each operation which needs to send a notification submits the message to the notification queue, and once it is submitted, the operation does not have any need to know how the notification is sent out. The centralized messaging process determines whether the message needs to be sent out immediately or should be aggregated with similar messages. For messages that can be aggregated, the messaging processor—which runs as a SQL Agent job—takes all the notifications for a single recipient and combines them once per day into a single e-mail.

share|improve this answer
    
I have another issue of creating profile for each user in DBMail. I've thousands of users in my system, is there any workaround to skip creating profiles for all users and make it dynamic? –  JPReddy Aug 20 '11 at 2:58
    
While it is possible to configure send profiles programatically, I don't understand why you would want to send messages from the application as though they were coming from the users themselves. I'm sure you've seen plenty of e-mail alerts that come from an address like "notification@mycompany.com" or "no-reply@mycompany.com" ... the situation you're using is exactly what those kind of addresses are for. You do not need to create profiles for recipients. (The application I described above uses a "notification" account for sending messages, for example.) –  Boris Nikolaevich Aug 20 '11 at 3:53
    
Did you have any other questions or need clarification before accepting an answer? –  Boris Nikolaevich Aug 22 '11 at 15:34
add comment

Why would you build a table to log the emails into, then have another process come through and read the emails from the table in order to send them via database mail.

Why not just modify your application to send the emails directly via database mail.

Database mail uses SQL Service Broker under the covers to handle the email sending. When you tell it to send an email it drops the email into a queue, then a background process comes through and grabs the email and actually sends it. In your current design:

  1. You drop the message into a table
  2. A process reads the email
  3. That process sends it to database mail
  4. Database mail puts it into a queue
  5. Database mail reads from the queue and sends the email

Why not remove steps 1, 2 and 3 from the process.

share|improve this answer
add comment

Is there any reason you need to store unsent emails inside SQL Server?

MailServer on Windows Servers have a mail pickup directory. Emails that need to be send will be stored in that directory. The mail server would pick them up automatically, if it has the time to do it.

For instance, if you generate a newsletter with 10k recipients, you would generate 10k files in the pick up directory.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.