{"id":63,"date":"2013-04-15T12:52:00","date_gmt":"2013-04-15T17:52:00","guid":{"rendered":"https:\/\/www.chrystalsander.com\/blog\/?p=63"},"modified":"2023-07-26T12:25:24","modified_gmt":"2023-07-26T17:25:24","slug":"sql-server-difference-between-sp_send_dbmail-xp_sendmail","status":"publish","type":"post","link":"https:\/\/www.chrystalsander.com\/blog\/?p=63","title":{"rendered":"SQL Server : Difference Between SP_SEND_DBMAIL & XP_SENDMAIL"},"content":{"rendered":"


<\/span><\/p>\n

\nRecently, I decided to send email notifications via SQL Server\u2019s XP_SENDMAIL()<\/a> 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.<\/span><\/div>\n
\n
<\/span><\/div>\n
\nAfter 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()<\/a>, then the email would be sent regardless of the end result of the transaction.<\/span><\/div>\n
\n
<\/span><\/div>\n
\nTherefore; 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. <\/span><\/div>\n
\n
<\/span><\/div>\n
\nIt appears that other important differences between the two email methods are:<\/span><\/div>\n
\n1.      XP_SENDMAIL requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.<\/span><\/div>\n
\n2.      SP_SEND_DBMAIL is an SMTP solution, added for SQL Server 2005 and later.<\/span><\/div>\n
\n
<\/span><\/div>\n
\nHappy coding.<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

Recently, I decided to send email notifications via SQL Server\u2019s 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 … Continue reading “SQL Server : Difference Between SP_SEND_DBMAIL & XP_SENDMAIL”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/63"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=63"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":177,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions\/177"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}