Report and SSRS - Get Report Cache Policy

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 defined cache policies against 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_GetReportCachePolicy
GO

CREATE PROC usp_GetReportCachePolicy @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retrieve a list of defined cache policies against one or
           many reports.
          
           Uses the Catalog, and CachePolicy 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 ReportFolder,
    
cp.cacheexpiration     AS CacheExpiration,
    
cp.expirationflags     AS ExpirationFlags
FROM  ReportServer.dbo.CachePolicy cp (NOLOCK)
INNER JOIN  ReportServer.dbo.Catalog c (NOLOCK)
  
ON  c.itemid = cp.reportid
-- no restriction on CatalogType added as cache policy is just for reports
WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'

ċ
usp_GetReportCachePolicy.sql
(1k)
Andy Hughes,
Jul 28, 2012, 10:10 AM
Comments