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

udfGetFilenameAndExtOnly()

Here’s a simple user-defined function (UDF) to return the filename and extension, when you are passed in the whole path with filename.

For example, this

c:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf

returns this

MSDBData.mdf

This function will be referenced by the next post.

USE Admin

GO

IF OBJECT_ID('dbo.udfGetFilenameAndExtOnly') Is Not Null

      DROP FUNCTION dbo.udfGetFilenameAndExtOnly

GO

CREATE FUNCTION dbo.udfGetFilenameAndExtOnly(

      @In varchar(1000)

      )

RETURNS varchar(300)

AS

/*    DATE              AUTHOR            REMARKS

      3/3/12                  PPaiva            Initial creation.

      DESCRIPTION

            Returns filename and extention only.  That is, path is stripped away.

      USAGE

            SELECT dbo.udfGetFilenameAndExtOnly('c:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf')

            SELECT dbo.udfGetFilenameAndExtOnly('Admin_Log.ldf')

      DEBUG

            -- Last backslash, in reverse

            SELECT CharIndex('\', Reverse('c:\Program Files\DATA\MSDBData.mdf'))

            -- Pos of last backslash

            SELECT Len('c:\Program Files\DATA\MSDBData.mdf') - 13

*/

BEGIN

      DECLARE @Out varchar(300),

                  @Pos smallint

           

      -- Strip away path if it exists

      -- Get position of last backslash

      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1

            BEGIN

                  SET @Pos = Len(@In) - @Pos + 2

                  SET @Out = Substring(@In, @Pos, 500)

            END

      ELSE

            SET @Out = @In

      RETURN @Out

END