Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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
rhut
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
Thank you for your detail and concise directions!! I will place this article in my briefcase for future use.
David Bird
David Bird
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 1190
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
equerystrian
equerystrian
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 199
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
JasonYousef
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 676
Thanks so much, great article....
MaricopaJoe
MaricopaJoe
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 404
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
SQLRNNR
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23173 Visits: 18271
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

andrew_chernoff
andrew_chernoff
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
MaricopaJoe
MaricopaJoe
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 404
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
Darwin101
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 575
Great article/fix, thanks :-D Just to let yo know the png files do not display. Ermm
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search