Stored Procedure to maintain RSExecutionLog

,

If you prefer to use TSQL to SSIS, this script can replace the SSIS package referred to in http://msdn.microsoft.com/en-us/library/aa964131(SQL.90).aspx for maintain the RSExecutionLog database.

To setup RSExecutionLog follow the instructions for steps of "Create and Load the Execution Log Database" 1 to 7, and then instead of step 8 - 10 just run my script above.

You can then simply schedule a new job to execute the dbo.Upd_RSExecutionLog stored procedure as required to keep your RSExecutionLog database upto date.

Load the sample reports and away you go.

Hope this is of use to people out there that struggle to deploy and debug SSIS - especially when it's someone else's code.

 

Acknowledgements:

Splitter function adapted from http://blog.magenic.com/blogs/whitneyw/archive/2008/10/08/Split-a-string-in-TSQL-_2800_without-looping_2900_.aspx

 

USE RSExecutionLog
GO

IF OBJECT_ID('dbo.Numbers') IS NOT NULL 
DROP TABLE dbo.Numbers;
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
);

SET NOCOUNT ON;

DECLARE
 @max AS INT,
 @rc AS INT;

SET @max = 10000;
SET @rc = 1;

BEGIN TRAN;
 INSERT INTO dbo.Numbers(number) VALUES(1);

 WHILE @rc * 2 <= @max
 BEGIN
 INSERT INTO dbo.Numbers(number)
 SELECT number + @rc 
 FROM dbo.Numbers;

 SET @rc = @rc * 2;
 END

 INSERT INTO dbo.Numbers(number)
 SELECT number + @rc 
 FROM dbo.Numbers
 WHERE number + @rc <= @max;
COMMIT TRAN; 
GO

IF OBJECT_ID('dbo.Split') IS NOT NULL 
DROP FUNCTION dbo.Split;
GO

CREATE FUNCTION dbo.Split ( 
 @List nvarchar(max), --The delimited list 
 @Del1 char(1) = '&', --The delimiter between each name value pair
 @Del2 char(1) = '=' --The delimiter between name and value
) 
RETURNS @T TABLE (Name nvarchar(2000), Value nvarchar(2000)) 
AS 
BEGIN 

 DECLARE @WrappedList nvarchar(max), @MaxItems int 

 SELECT @WrappedList = @Del1 + @List + @Del1, @MaxItems = LEN(@List) 

 INSERT INTO @T (Name, Value) 
 SELECT CAST(SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del2, @WrappedList, Number + 1) - Number - 1) AS NVARCHAR(2000)) AS Name
 ,CAST(SUBSTRING(@WrappedList, CHARINDEX(@Del2, @WrappedList, Number + 1) + 1, CHARINDEX(@Del1, @WrappedList, Number + 1) - CHARINDEX(@Del2, @WrappedList, Number + 1) - 1) AS NVARCHAR(2000)) AS Value 
 FROM dbo.Numbers n 
 WHERE n.Number <= LEN(@WrappedList) - 1 
 AND SUBSTRING(@WrappedList, n.Number, 1) = @Del1 

RETURN 
END
GO

IF OBJECT_ID('dbo.Upd_RSExecutionLog') IS NOT NULL 
DROP PROCEDURE dbo.Upd_RSExecutionLog;
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 WITH(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 WITH(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 WITH (NOLOCK)
     INNER JOIN 
 ReportServer.dbo.ExecutionLog l WITH (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 WITH(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 WITH(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 WITH(NOLOCK)
 INNER JOIN
 ReportServer.dbo.Catalog C WITH(NOLOCK)
 ON L.ReportID = C.ItemID
 INNER JOIN
 RSExecutionLog.dbo.Reports R WITH(NOLOCK)
 ON C.Name COLLATE Latin1_General_CI_AS = R.Name
 AND C.Path COLLATE Latin1_General_CI_AS = R.Path
 AND C.Type = R.ReportType
 INNER JOIN
 RSExecutionLog.dbo.Users U WITH (NOLOCK)
 ON L.UserName COLLATE Latin1_General_CI_AS = U.UserName
 INNER JOIN
 RSExecutionLog.dbo.Machines M WITH(NOLOCK)
 ON L.InstanceName COLLATE Latin1_General_CI_AS = M.InstanceName
 INNER JOIN
 RSExecutionLog.dbo.FormatTypes F WITH(NOLOCK)
 ON L.Format COLLATE Latin1_General_CI_AS = F.Format
 INNER JOIN
 RSExecutionLog.dbo.StatusCodes S WITH(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, '&', '=')) 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

-- Test it
EXEC dbo.Upd_RSExecutionLog
GO

Rate

5 (2)

Share

Share

Rate

5 (2)