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:
SELECT mailitem_id, subject, sent_status ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
