difference xp_sendmail and sp_send_dbmail proc

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.