7

Can I ask what is the difference between xp_sendmail and sp_send_dbmail proc? They are both send e-mail message, which may include a query result set attachment, to the specified recipients.....

What is the difference?

gbn
  • 422,506
  • 82
  • 585
  • 676

3 Answers3

17

xp_sendmail requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.

sp_send_dbmail is a simple SMTP solution, added for SQL Server 2005+

sp_send_dbmail is by far better.

gbn
  • 422,506
  • 82
  • 585
  • 676
7

Another difference between the two is that email message sent using sp_send_dbmail() will be rolled back (not sent) if the transaction is rolled back. This does not happen with email sent using xp_sendmail().

So, if you want the email message to be sent regardless of the end result of the transaction you'll need to use xp_sendmail().

I was sending emails to notify users if an SP was unable to complete it's processing. Of course, I was rolling back the transaction in that event. When I switched to sp_send_dbmail() the transactions that were being rolled back (the very ones I wanted to get an email notification from) stopped sending emails.

Stephen Study
  • 113
  • 2
  • 6
  • Even if the messages have to be sent regardless of transaction rollback, it is possible to use `sp_send_dbmail`. Store all information in a table variable, and send after rollback. – ajeh Mar 21 '17 at 21:21
0

we don't control the calling code - it's closed and calls the sp in a transaction. We need a feature in SQL Server to send direct or flush the mail queue.

The Danish
  • 1
  • it's related to one of the differences, queing in sp_send_dbmail, and the issue when sending email from a transaction that is later rolled back. In our case we don't do the rollback, so cannot store anything. It's related to the ajeh Mar 21 at 21:21 comment. – The Danish Jul 05 '17 at 22:46