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




Saturday, March 3, 2012

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