{"id":9770,"date":"2020-09-20T08:35:01","date_gmt":"2020-09-19T22:35:01","guid":{"rendered":"https:\/\/database.guide\/?p=9770"},"modified":"2020-09-22T09:01:25","modified_gmt":"2020-09-21T23:01:25","slug":"check-for-failed-email-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/check-for-failed-email-in-sql-server-t-sql\/","title":{"rendered":"Check for Failed Email in SQL Server (T-SQL)"},"content":{"rendered":"\n

If you’re trying to send mail with SQL Server<\/a> but it’s not being delivered, try checking the sysmail_faileditems<\/code> view.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example of checking for failed mail. Note that it needs to be run on the msdb<\/code> database.<\/p>\n\n\n\n

SELECT * FROM msdb.dbo.sysmail_faileditems;<\/code><\/pre>\n\n\n\n
Result (using vertical output):<\/p>\n\n\n\n
mailitem_id                 | 1\nprofile_id                  | 1\nrecipients                  | homer@example.com\ncopy_recipients             | NULL\nblind_copy_recipients       | NULL\nsubject                     | SQL Server Agent Job: FAILED\nbody                        | Your favorite SQL Server Agent job just failed\nbody_format                 | TEXT\nimportance                  | NORMAL\nsensitivity                 | NORMAL\nfile_attachments            | NULL\nattachment_encoding         | MIME\nquery                       | NULL\nexecute_query_database      | NULL\nattach_query_result_as_file | 0\nquery_result_header         | 1\nquery_result_width          | 256\nquery_result_separator      |  \nexclude_query_output        | 0\nappend_query_error          | 0\nsend_request_date           | 2020-08-24 02:40:48.093\nsend_request_user           | sa\nsent_account_id             | NULL\nsent_status                 | failed\nsent_date                   | 2020-08-24 02:41:53.000\nlast_mod_date               | 2020-08-24 02:41:53.290\nlast_mod_user               | sa<\/pre>\n\n\n\n
I used vertical output here so that you don’t need to scroll sideways to see all columns.<\/p>\n\n\n\n
In this case, I’m showing one failed email. I actually had two failed emails but I decided that it was more concise to show just one result.<\/p>\n\n\n\n
In my case, I had used the wrong mail server in the code that sends the email, and the emails failed. As soon as I updated the code to use the correct mail server, all subsequent emails were sent successfully. However, this doesn’t change those that had already failed, and therefore, they remain in the sysmail_faileditems<\/code> view.<\/p>\n\n\n\n
You can query sysmail_unsentitems<\/code> to return a list of unsent emails<\/a> (ones that are yet to be sent, not necessarily failed). <\/p>\n\n\n\n
You can also query sysmail_sentitems<\/code> to get all sent emails<\/a>.<\/p>\n\n\n\n
You can also query sysmail_allitems<\/code> to get all emails<\/a> (sent, unsent, failed, and retrying).<\/p>\n","protected":false},"excerpt":{"rendered":"
If you’re trying to send mail with SQL Server but it’s not being delivered, try checking the sysmail_faileditems view.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,104,61],"class_list":["post-9770","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-send-email","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=9770"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9770\/revisions"}],"predecessor-version":[{"id":10067,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9770\/revisions\/10067"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=9770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=9770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=9770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}