Get Report Snapshot History
Applicability:
SQL Server 2000: Not Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Sorna Kumar Muthuraj
Date: 9 Jul 2011
Description
Retrieves a list of snapshot history for one or many reports.
NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'
Code
Stored Procedure:
DROP PROC usp_GetReportSnapshotHistory
GO
CREATE PROC usp_GetReportSnapshotHistory @ReportName NVARCHAR(850) = NULL
AS
/****************************************************
Purpose: To retrieve a list of snapshot history for one or
many reports.
Uses the Catalog, Schedule, ReportSchedule and SnapshotData tables
in the report server database (assumed to be called by its default
name of 'ReportServer')
Author: Sorna Kumar Muthuraj
History: 9 Jul 2011 - Initial Issue
*****************************************************/
SELECT
c.itemid AS ReportID,
c.name AS ReportName,
c.path AS ReportPath,
CONVERT(VARCHAR(20), h.snapshotdate, 113) AS SnaphsotDate,
s.DESCRIPTION AS SnapshotDescription,
s.effectiveparams AS SnapshotEffectiveParams,
s.queryparams AS SnapshotQueryParams,
sc.name AS ScheduleName,
CONVERT(VARCHAR(20), sc.nextruntime, 113) AS ScheduleNextRunTime
FROM ReportServer.dbo.History h (NOLOCK)
INNER JOIN ReportServer.dbo.SnapshotData s (NOLOCK)
ON h.snapshotdataid = s.snapshotdataid
INNER JOIN ReportServer.dbo.Catalog c (NOLOCK)
ON c.itemid = h.reportid
INNER JOIN ReportServer.dbo.ReportSchedule rs (NOLOCK)
ON rs.reportid = h.reportid
INNER JOIN ReportServer.dbo.Schedule sc (NOLOCK)
ON sc.scheduleid = rs.scheduleid
WHERE rs.reportaction = 2 -- Create schedule
AND c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
c.name,
h.snapshotdate