What's the best way send email from the database in SQL Server 2008 R2?

Question

Migrating a legacy database from SQL Server 2000, and there are a few scheduled tasks that use SP_OACreate & CDOSYS to send emails to an open SMTP relay as in this KB article: http://support.microsoft.com/kb/312839

In SQL Server 2008 R2, what's the best (simplest) way to replace the sendmail functionality? It seems like DB Mail is the new best recommended way, anyone have much experience with it or another alternative?

Answer 1

Yes, DB Mail is by far the best way: it's simple SMTP.

Don't think about using:

• sp_OA% and cdosys (sp_OA% can be tricky and leak resources)
• xp_sendmail (require local Outlook install for MAPI client)

Answer 2

Yes, I use sp_send_dbmail primarily for sending mail within SQL Server, and it seems to be easy enough to set up and a reliable option, with plenty of configuration available.

Answer 3

Setup the SQL Server using DB Mail. It is fairly simple and uses SQL Service broker to manage messages - which means you don't lose mail if you can't connect to the SMTP server.