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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Scott Duncan » Implementing Execution Log Reports on 32-bit...

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

Rate Topic

Display Mode

Topic Options

Author

Message

The CosmicTrickster

Posted Tuesday, November 23, 2010 9:08 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:10 PM
Points: 2,605, Visits: 756

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

Post #1025645

rhut

Posted Wednesday, November 24, 2010 7:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 6:48 AM
Points: 35, Visits: 52

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

Post #1025887

David Bird

Posted Wednesday, November 24, 2010 7:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:29 PM
Points: 182, Visits: 952

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

My PC Quick Reference Guide

Post #1025913

equerystrian

Posted Wednesday, November 24, 2010 7:39 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 15, 2013 1:19 PM
Points: 14, Visits: 181

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.

Post #1025922

JasonYousef

Posted Wednesday, November 24, 2010 9:09 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 68, Visits: 558

Thanks so much, great article....

Post #1025984

MaricopaJoe

Posted Wednesday, November 24, 2010 9:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 9:17 AM
Points: 49, Visits: 233

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

JOE FEIGELMAN | Database Developer | Insight | www.Insight.com
t. 480.409.6666 c. 623.428.9421 e. Joe.Feigelman@insight.com

Post #1025990

SQLRNNR

Posted Wednesday, November 24, 2010 3:22 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the article.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1026230

andrew_chernoff

Posted Thursday, December 23, 2010 1:14 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 03, 2011 7:51 AM
Points: 1, Visits: 2

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.

Post #1038930

MaricopaJoe

Posted Thursday, December 23, 2010 4:16 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 9:17 AM
Points: 49, Visits: 233

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/

JOE FEIGELMAN | Database Developer | Insight | www.Insight.com
t. 480.409.6666 c. 623.428.9421 e. Joe.Feigelman@insight.com

Post #1038973

Darwin101

Posted Tuesday, December 28, 2010 12:19 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:45 PM
Points: 18, Visits: 398

Great article/fix, thanks BigGrin

Just to let yo know the png files do not display. Ermm

Post #1039937

« Prev Topic | Next Topic »

Permissions