vwMailSent

This view is helpful in examining mail that has been sent to the mail queue.

Use Admin

IF object_id('dbo.vwMailSent') Is Not Null

      DROP VIEW dbo.vwMailSent

GO

CREATE VIEW dbo.vwMailSent

AS

/*    DATE        AUTHOR            REMARKS

      3/10/11           PPaiva            Initial creation.

     

     

      SELECT *

      FROM vwMailSent

      ORDER BY 1 desc

     

      SELECT *

      FROM vwMailSent

      WHERE Status = 'failed'

      ORDER BY 1 desc

     

*/

SELECT  a.mailitem_id,

            a.send_request_date,

            a.sent_date,

            a.sent_status,

         CASE sent_status

                        WHEN 0 THEN 'unsent'

                        WHEN 1 THEN 'sent'

                        WHEN 3 THEN 'retrying'

                        ELSE 'failed'

                  END as Status,

            log.description,

            a.profile_id,

            a.recipients,

            dbo.udfGetFilenameAndExtOnly(a.file_attachments) Attachment,

            a.file_attachments,

            a.copy_recipients,

            a.blind_copy_recipients, a.subject,

            a.from_address,

            a.reply_to,

            a.body,

            a.body_format,

            a.importance,

            a.sensitivity,

            a.attachment_encoding,

            a.query,

            a.execute_query_database,

            a.attach_query_result_as_file,

            a.query_result_header,

            a.query_result_width,

            a.query_result_separator,

            a.exclude_query_output,

            a.append_query_error,

            a.send_request_user,

            a.sent_account_id,

            a.last_mod_date,

            a.last_mod_user,

            log.log_id,

            log.event_type,

            log.log_date,

            log.process_id,

            --log.mailitem_id,

            log.account_id

            --log.last_mod_date,

            --log.last_mod_user

FROM msdb.dbo.sysmail_mailitems a

LEFT JOIN msdb.dbo.sysmail_log log

      ON log.mailitem_id = a.mailitem_id

GO