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?

score 3 · Answer 1 · edited Dec 08 '15 at 04:42

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.

edited Dec 08 '15 at 04:42

Greg

515
1
8
19

answered Jun 16 '11 at 05:48

Haroon

1,052
13
28

score 3 · Answer 2 · answered Jun 16 '11 at 05:48

3

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.

answered Jun 16 '11 at 05:48

Jerad Rose

15,235
18
82
153

score 3 · Accepted Answer · answered Jun 16 '11 at 05:48

3

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)

answered Jun 16 '11 at 05:48

gbn

422,506
82
585
676

1

thanks, all the answers were pretty similar but I accepted yours as you addressed the deficiencies of the alternatives as well. cheers! – matao Jun 16 '11 at 07:37

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

3 Answers3