November 23, 2010 at 9:08 pm
Comments posted to this topic are about the item Implementing Execution Log Reports on 32-bit SQL Server 2008 R2
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 24, 2010 at 7:08 am
Thank you for your detail and concise directions!! I will place this article in my briefcase for future use.
November 24, 2010 at 7:32 am
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
November 24, 2010 at 7:39 am
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.
November 24, 2010 at 9:09 am
Thanks so much, great article....
November 24, 2010 at 9:19 am
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
November 24, 2010 at 3:22 pm
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
December 23, 2010 at 1:14 pm
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.
December 23, 2010 at 4:16 pm
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.
December 28, 2010 at 12:19 pm
Great article/fix, thanks 😀 Just to let yo know the png files do not display. :ermm:
July 18, 2014 at 4:34 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply