About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Saturday, March 10, 2012

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




No comments:

Post a Comment