How to Send Email with SQL Server’s sp_send_dbmail
So you’re writing T-SQL code and you’ve decided that you want to send emails to customers, employees, or the general public. Perhaps you need to send order updates or low stock notifications.
Stop right there. You don’t really wanna do that with SQL Server. Let’s talk about why.
It’s not easy to troubleshoot. SQL Server’s database mail doesn’t have the kind of robust troubleshooting tools necessary to understand why specific emails are delayed or non-deliverable. This is a really important task for customer-facing emails: your customers are gonna say, “I’m not getting your emails,” and your manager is gonna ask why, and you’re not going to have good answers.
Email deliverability is hard. These days, anti-spam and antivirus vendors are doing all kinds of tricks to suss out which emails are sent by humans versus which ones are automated. It’s very easy for your SQL Server’s IP address to end up on blacklists, and depending on how the blacklist is managed, your outgoing emails can also affect the deliverability of your company’s human emails, too. You don’t want your company’s email admins to say, “Well, folks, looks like our emails are all getting marked as spam because one of our developers messed up our sending reputation.”
There are legal requirements on commercial emails. You need to comply with things like the CAN-SPAM Act, giving recipients a clear unsubscribe link that actually works. If users don’t see that, they’ll simply mark your email as spam – and boom, your deliverability rates get shot. Malicious users (like me) simply block any email that doesn’t comply with the CAN-SPAM Act. I don’t give a damn about your company’s email deliverability problems – if you don’t care enough to comply with the law, I’m blocking all further emails from your company.
SQL Server doesn’t make any of that easy. Instead, build your emails in app code like C# or Java, and use transactional email providers like Sendgrid or Postmark to make sure the emails are legal and they get delivered to the right inboxes. You’re cursing me right now because I just made your job a little bit harder, but trust me, you’re going to thank me a year from now.
12 Comments. Leave new
But SQL Server can post to URLs now – including to external email providers – CHECKMATE!
Yes, but…
“It’s not easy to troubleshoot.”
“Email deliverability is hard.”
“There are legal requirements on commercial emails.”
“SQL Server doesn’t make any of that easy.”
Brent’s article is spot on (as usual)!
You could have mentioned that it may be okay to send mails internal, e.g. to your developers when errors occur.
Or that the SQL Server Agent uses this stuff, when you set up alerts or enabled the option in the job to send mails on success / failure etc.
But I agree 100% that it should never be used for external communication (not even a reset password request etc.)
Haha, I think Brent invited this kind of comment. Easy to forget the first sentence while reading on…
@torgeir – hahaha, bingo.
@Thomas – make sure to reread that first sentence of the blog post to understand the audience.
It’s still generally not going to get you as much benefit as leveraging some sort of dashboard or other observability system.
When you’re sending those emails to internal users, even if it’s your own team, you have to manage recipient lists, ensuring that the service account has an email address provisioned, and (possibly) working around anti-impersonation measures, not to mention credential configuration.
While email is built on mature, stable protocols, never forget that it is a “best-effort” delivery system – meaning there’s no guarantee on when or whether any given message will arrive.
100% Stop sending automated emails. One more reason to block the SQL Server from the email relay – security. Wouldn’t take much to send sensitive information out to a threat actor.
Older versions of SQL Server sp_send_dbmail used some kind of Outlook Express integration to send the emails.
This could get jammed up stopping new emails from being sent, and the only way to fix it was to restart SQL Server! eeek! not good in production!
at the time we implemented an email queue in table or two in SQL Server – so it was easy to see “what” emails were being generated for troubleshooting/customer support etc.
Initially the queue was enumerated/processed by a SQL agent job and an external DLL to send the emails
Later replaced by an application/service that did the same
agreed it’s even better to use an email sending provider
getting ones own IPs etc. off black lists can be painful
Also: if you are using Outlook365 or GSuite (and you probably are) SQL Server cannot send your e-mails because of the 2FA.
https://www.jeeja.biz/2021/07/01/sql-server-why-your-database-mail-is-not-working-and-probably-never-will/
[…] Brent Ozar lays out an argument: […]
I don’t see the sp_send_dbmail code you wrote to copy into my project…
This is a great breakdown of the essential components of a database! The explanations are clear and helpful, especially for anyone new to the topic. It’s important to understand these basics, and your blog makes it easy to grasp. Keep up the great work with such informative content