Prev: Trouble with CRM drill-through from a sub report.
Next: rpt to rdl conversion
From: rmarcotte on 15 Feb 2007 15:30
Has anyone worked with Reporting Services Catalog? We need to be
able to cache reports after first use, but we also need to clear the
cache as new data is loaded during the day. The following works,
but I wanted to see if anyone has taken this approach?

UPDATE SN
SET SN.PermanentRefcount = SN.PermanentRefcount - 1
FROM
[ReportServerTempDB].dbo.SnapshotData AS SN
INNER JOIN [ReportServerTempDB].dbo.ExecutionCache AS EC ON
SN.SnapshotDataID = EC.SnapshotDataID
INNER JOIN Catalog AS C ON EC.ReportID = C.ItemID
WHERE C.Path like '/Foldername/%'

DELETE EC
FROM
[ReportServerTempDB].dbo.ExecutionCache AS EC
INNER JOIN Catalog ON EC.ReportID = Catalog.ItemID
WHERE Catalog.Path like '/Foldername/%'

This is taken from one of the reportserver catalog stored procedures
(it's modified to clear an entire folder's worth of reports).

thanks,

 | 
Pages: 1
Prev: Trouble with CRM drill-through from a sub report.
Next: rpt to rdl conversion