0845 643 64 63

Clearing SSRS Query cache

When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data.

A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The next time you preview the report SSRS will be forced to requery the source.

To save time, I use the following macro to take care of it.

Press ALT+F8 to open the Macro Explorer, and add a new module called “RemoveRDLDataFiles” under MyMacros. Edit the file and add the following code to the file. (This is for SQL Server 2008, you may need to tweak the references for 2005).

Imports System Imports EnvDTE Imports EnvDTE80 Imports EnvDTE90 mports System.Diagnostics Imports System.IO Public Module RemoveRDLDataFiles Sub RemoveRDLDataFiles() Dim project As Project Dim Folder As String project = DTE.ActiveSolutionProjects(0) Dim fi As New FileInfo(project.FullName.ToString) Folder = fi.DirectoryName For Each FileFound As String In Directory.GetFiles(Folder, "*.rdl.data") File.Delete(FileFound) Next End Sub End Module

You can then run it by either double clicking on the macro, or assigning a keyboard shortcut to it (via Tools, Customize, Keyboard).

3 Responses to Clearing SSRS Query cache

  • Another option is to use BIDS Helper:
    http://bidshelper.codeplex.com/wikipage?title=Delete%20Dataset%20Cache%20Files&referringTitle=Home

    • Thanks Greg
      I use (and highly recommend) BIDS Helper – and never new that option was there. Live and Learn!
      Alex

  • Pingback: Where did my RDL.DATA files go? - Some Random Thoughts

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

365,078 Spambots Blocked by Simple Comments

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon