Microsoft SQL Server 2014 Unleashed

Viewing Mail Message Data

The second set of msdb objects (and perhaps the more important ones) we’ll review are those used to discover the status of mail messages.

The first thing you need to do is to check on the status of the mail messages you’ve attempted to send, without relying on inboxes to tell you if they’ve been received. Several views in msdb enable this, most of which may be filtered by mail account, sending user, send date, status, and more. To begin this process, you query the view sysmail_allitems, which contains all the data about your messages (subjects, recipients, importance, and so on) as well as send_request_date, sent_date, and sent_status. Here’s an example:

Click here to view code image

SELECT mailitem_id, subject, sent_status ...