Recently, I decided to send email notifications via SQL Server’s XP_SENDMAIL() if a stored procedure were unable to complete its processing. For the same stored procedure, I was ALSO rolling back the transaction in the event of failure. However, I noticed that whenever the transactions failed, then I would not get the email.
After some research, I discovered that email messages sent using XP_SENDMAIL() would be rolled back (not sent) if the transaction is rolled back. However, I learned that if I were to use another SQL Server method, SP_SEND_DBMAIL(), then the email would be sent regardless of the end result of the transaction.
Therefore; in sum, if you want the email message to be sent regardless of the end result of the transaction you’ll need to use SP_SEND_DBMAIL(). Also, since XS_SENDMAIL() is apparently going to be deprecated, SP_SEND_DBMAIL may be the way to go for you.
It appears that other important differences between the two email methods are:
1. XP_SENDMAIL requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.
2. SP_SEND_DBMAIL is an SMTP solution, added for SQL Server 2005 and later.
Happy coding.