Implementing Execution Log Reports on 32-bit SQL Server 2008 R2

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    Comments posted to this topic are about the item Implementing Execution Log Reports on 32-bit SQL Server 2008 R2



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • rhut

    Valued Member

    Points: 61

    Thank you for your detail and concise directions!! I will place this article in my briefcase for future use.

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Thanks for the article. It motivated me to look more into this sample tool and maybe make use of it.

    At first I did not know what Execution Log Reports was for. For those like me, it is a package used to extract data from the SSRS ExecutionLog table to another table in a more readable format.

    This other table is then used instead of the ExecutionLog table for queries about performance and other stuff. Using the second table also prevents locks on SSRS.

    Thats what I found out after a little bit a digging. Please correct me if I am wrong.

    Happy Thanksgiving

    David Bird

  • equerystrian

    SSChasing Mays

    Points: 642

    Thanks very much for this - I never would have guessed there would have been so many issues moving from 2005 to 2008 R2! We're moving in January, so I'm definitely filing this for reference again then.

  • JasonYousef

    SSCommitted

    Points: 1769

    Thanks so much, great article....

  • MaricopaJoe

    Ten Centuries

    Points: 1169

    We are using this proc instead.

    USE [RSExecutionLog]

    GO

    /****** Object: StoredProcedure [dbo].[Upd_RSExecutionLog] Script Date: 11/23/2010 09:57:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[Upd_RSExecutionLog]

    AS

    --***********************************

    -- Initialise

    --***********************************

    DECLARE @myRows INT

    DECLARE @myTimeEnd DATETIME

    DECLARE @myTime DATETIME

    SET @myTimeEnd = (SELECT MAX(TimeEnd) FROM RSExecutionLog.dbo.ExecutionLogs)

    SET @myTime = GETDATE()

    --***********************************

    -- Lookup Tables

    --***********************************

    -- FormatTypes

    INSERT INTO RSExecutionLog.dbo.FormatTypes

    (Format)

    SELECT DISTINCT

    Format

    FROM ReportServer.dbo.ExecutionLog (NOLOCK)

    WHERE Format IS NOT NULL

    AND Format COLLATE Latin1_General_CI_AS NOT IN (SELECT Format

    FROM RSExecutionLog.dbo.FormatTypes)

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table FormatTypes'

    ,@myTime)

    -- Machines

    INSERT INTO RSExecutionLog.dbo.Machines

    (InstanceName)

    SELECT DISTINCT

    InstanceName

    FROM ReportServer.dbo.ExecutionLog (NOLOCK)

    WHERE InstanceName IS NOT NULL

    AND InstanceName COLLATE Latin1_General_CI_AS NOT IN (SELECT InstanceName

    FROM RSExecutionLog.dbo.Machines)

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Machines'

    ,@myTime)

    -- Reports

    INSERT INTO RSExecutionLog.dbo.Reports

    (ReportID

    ,Path

    ,Name

    ,ReportType)

    SELECT DISTINCT

    c.ItemID

    ,c.Path

    ,c.Name

    ,c.Type

    FROM ReportServer.dbo.Catalog c (NOLOCK)

    INNER JOIN

    ReportServer.dbo.ExecutionLog l (NOLOCK)

    ON l.ReportID = c.ItemID

    WHERE c.ItemID NOT IN (SELECT ReportID

    FROM RSExecutionLog.dbo.Reports)

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Reports'

    ,@myTime)

    -- ReportTypes - Inserted via the setup createtables.sql file

    --INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (1, N'Folder')

    --INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (2, N'Report')

    --INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (3, N'Resource')

    --INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (4, N'Linked Report')

    --INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (5, N'Data Source')

    -- RequestTypes - Inserted via the setup createtables.sql file

    --INSERT INTO RSExecutionLog.[dbo].[RequestTypes] ([RequestType], [Name]) VALUES (0, N'User')

    --INSERT INTO RSExecutionLog.[dbo].[RequestTypes] ([RequestType], [Name]) VALUES (1, N'System')

    -- SourceTypes - Inserted via the setup createtables.sql file

    --INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (1, N'Live')

    --INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (2, N'Cache')

    --INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (3, N'Snapshot')

    --INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (4, N'History')

    --INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (5, N'Adhoc')

    -- StatusCodes

    INSERT INTO RSExecutionLog.dbo.StatusCodes

    (Status)

    SELECT DISTINCT

    Status

    FROM ReportServer.dbo.ExecutionLog (NOLOCK)

    WHERE Status IS NOT NULL

    AND Status COLLATE Latin1_General_CI_AS NOT IN (SELECT Status

    FROM RSExecutionLog.dbo.StatusCodes)

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table StatusCodes'

    ,@myTime)

    -- Users

    INSERT INTO RSExecutionLog.dbo.Users

    (UserName)

    SELECT UserName

    FROM ReportServer.dbo.Users (NOLOCK)

    WHERE UserName COLLATE Latin1_General_CI_AS NOT IN (SELECT UserName

    FROM RSExecutionLog.dbo.Users)

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Users'

    ,@myTime)

    --***************************

    -- Main Tables

    --***************************

    -- ExecutionLogs

    INSERT INTO RSExecutionLog.dbo.ExecutionLogs

    (ReportKey

    ,UserKey

    ,MachineKey

    ,RequestType

    ,FormatType

    ,StatusCode

    ,SourceType

    ,Parameters

    ,TimeStart

    ,TimeEnd

    ,TimeDataRetrieval

    ,TimeProcessing

    ,TimeRendering

    ,ByteCount

    ,[RowCount])

    SELECT R.ReportKey

    ,U.UserKey

    ,M.MachineKey

    ,L.RequestType

    ,F.FormatType

    ,S.StatusCode

    ,L.Source AS SourceType

    ,L.Parameters

    ,L.TimeStart

    ,L.TimeEnd

    ,L.TimeDataRetrieval

    ,L.TimeProcessing

    ,L.TimeRendering

    ,L.ByteCount

    ,L.[RowCount]

    FROM ReportServer.dbo.ExecutionLog L (NOLOCK)

    INNER JOIN

    ReportServer.dbo.Catalog C (NOLOCK)

    ON L.ReportID = C.ItemID

    INNER JOIN

    RSExecutionLog.dbo.Reports R WITH(NOLOCK)

    ON c.ItemID = r.ReportID

    INNER JOIN

    RSExecutionLog.dbo.Users U (NOLOCK)

    ON L.UserName COLLATE Latin1_General_CI_AS = U.UserName

    INNER JOIN

    RSExecutionLog.dbo.Machines M (NOLOCK)

    ON L.InstanceName COLLATE Latin1_General_CI_AS = M.InstanceName

    INNER JOIN

    RSExecutionLog.dbo.FormatTypes F (NOLOCK)

    ON L.Format COLLATE Latin1_General_CI_AS = F.Format

    INNER JOIN

    RSExecutionLog.dbo.StatusCodes S (NOLOCK)

    ON L.Status COLLATE Latin1_General_CI_AS = S.Status

    WHERE L.TimeEnd > @myTimeEnd

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table ExecutionLogs'

    ,@myTime)

    -- ExecutionParameters

    INSERT INTO RSExecutionLog.dbo.ExecutionParameters

    (Name

    ,Value

    ,ExecutionLogID)

    SELECT P.Name ,P.Value ,L.ExecutionLogID

    FROM RSExecutionLog.dbo.ExecutionLogs L

    CROSS APPLY (SELECT Name ,Value FROM RSExecutionLog.dbo.Split(L.Parameters, '&','=')) as P

    WHERE L.Parameters IS NOT NULL

    SET @myRows = @@ROWCOUNT

    INSERT INTO RSExecutionLog.dbo.RunLogs

    ([Event]

    ,[When])

    VALUES

    ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table ExecutionParameters'

    ,@myTime)

    -- ExecutionLogs NULL out Parameters that have been processed

    UPDATE RSExecutionLog.dbo.ExecutionLogs

    SET Parameters = NULL

    WHERE Parameters IS NOT NULL

    -- Report updates

    SELECT [Event]

    ,[When]

    FROM RSExecutionLog.dbo.RunLogs

    WHERE [When] = @myTime

    RETURN

    GO

  • SQLRNNR

    SSC Guru

    Points: 281205

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • andrew_chernoff

    Grasshopper

    Points: 21

    Thank you for providing this clear and accurate information on getting the RSExecutionlog code working with SQL Server 2008 R2.

    I followed your steps and was able to resolve issues as documented 1 by 1 and get things working within an hour. It surely would have taken much longer to figure out otherwise. Nice work!

    One issue I had was the screen shots referenced in the Figures did not display, but this was not a showstopper.

  • MaricopaJoe

    Ten Centuries

    Points: 1169

    You guys might want to take a look at this as well. I blew the doors off my managers when I gave them this and the rsexecutionlog reports.

    http://www.sqlservercentral.com/articles/SSRS/69257/

  • Darwin101

    SSC Veteran

    Points: 289

    Great article/fix, thanks 😀 Just to let yo know the png files do not display. :ermm:

  • TrailRunner

    SSCommitted

    Points: 1540

    Hi,

    The folders below:

    Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports

    no longer exists from the CodePlex link. Does anyone know the updated path or where to get a copy from? Thanks.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply