Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implementing Execution Log Reports on 32-bit SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Tuesday, November 23, 2010 9:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:17 PM
Points: 2,644, Visits: 809
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
Posted Wednesday, November 24, 2010 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 24, 2010 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996
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
Posted Wednesday, November 24, 2010 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 7:32 AM
Points: 14, Visits: 195
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
Posted Wednesday, November 24, 2010 9:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:58 AM
Points: 70, Visits: 589
Thanks so much, great article....
Post #1025984
Posted Wednesday, November 24, 2010 9:19 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:33 PM
Points: 54, Visits: 290
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


Post #1025990
Posted Wednesday, November 24, 2010 3:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 20,466, Visits: 14,098
Thanks for the article.



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


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
Posted Thursday, December 23, 2010 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 01, 2013 2:55 PM
Points: 1, Visits: 3
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
Posted Thursday, December 23, 2010 4:16 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:33 PM
Points: 54, Visits: 290
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/
Post #1038973
Posted Tuesday, December 28, 2010 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:22 PM
Points: 18, Visits: 441
Great article/fix, thanks Just to let yo know the png files do not display.
Post #1039937
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse