Remove a SQL Server Reporting Services Report from Cache Using RS.EXE

By: Ray Barley   |   Updated: 2008-05-12   |   Comments (5)   |   Related: > Reporting Services Administration

Problem

If you take advantage of caching reports in SSRS, the cache expiration can be based on a time limit (e.g. 30 minutes) or a schedule (e.g. expire at midnight).  We have a situation where we refresh our data warehouse weekly and want to cache reports until the next weekly load.  Occasionally we will have to rerun a portion of the weekly data load and when this happens we need to expire all cached reports since the underlying data has changed.  We have looked around in SQL Server Management Studio and the Report Manager web application but have not found how to do this.  Can you provide the details to remove a report from the cache?

Solution

As you have stated the options for expiring a cached report are based on elapsed time or a schedule.  There is no built-in capability to automatically expire cached reports when the underlying data changes.  The tradeoff with caching is increasing performance versus minimizing the likelihood of a report showing stale data.   According to Books on Line you can only delete reports from the cache using the SOAP API (i.e. the SSRS web service).   Probably the simplest way to accomplish this task is to use the RS utility that comes with SSRS; you can also write the .NET code to call the web service and deploy it in a Windows Forms app, Windows Service, Web app, etc.  RS is a command line tool that executes Visual Basic .NET code.   You can find the RS utility in the folder C:\Program Files\Microsoft SQL Server\90\Tools\Binn (assuming you accepted the defaults during SSRS installation).  You simply write the VB.NET code, save it in a text file with an extension of .rss, then execute it with the RS utility.  You can use your favorite text editor to write the code; you do not need Visual Studio. 

Let's take a look at some VB.NET sample code that will remove a report from the cache:

Public Sub Main()
  If ReportPath Is Nothing Then
    Console.WriteLine("Parameter ReportPath not specified")
  Else
    rs.FlushCache(ReportPath)
  End If
End Sub

There are a couple of points to discuss about the above code:

• You must put your code in a function named Public Sub Main.  This is standard VB.NET and is the entry point where your code starts executing.  You are free to code other functions and call them from Sub Main.
• ReportPath is a global variable that contains the path to the report that you want to remove from the cache.  You do not declare the ReportPath variable in your code; doing so is an error.  ReportPath is passed in on the RS command line.  There is nothing special about the variable name ReportPath; you could use any name.
• Console.WriteLine displays the error message shown if the ReportPath parameter is not supplied on the command line.
• rs is a built-in variable that you use to execute methods on the SSRS web service.
• FlushCache is the web service method that removes a report from the cache.  The global variable ReportPath must contain the full path to the report to be deleted from the cache.

RS accepts a number of command line arguments; you can reference the complete list and description in Books on Line here.  At a minimum you will need to supply the following command line parameters to execute the above VB.NET code to remove a report from the cache:

• -i input_file
• -s serverURL
• -v globalvar

Assuming that you saved the above VB.NET code in a file named uncache.rss, the RS command line to execute the VB.NET code shown above to remove the report named CacheRpt in the Reports folder from the cache is as follows:

The serverURL parameter assumes that the Report Server is on the local machine; replace localhost with the name of the Report Server if necessary.

To test removing a report from the cache, we will do the following demo:

• Add a Textbox to a report to show the current data and time; we will use this to determine whether the report is being run from the cache.
• Configure the cache setting for the report.
• Execute the report a few times to verify that caching is working.
• Remove the report from the cache by executing our VB.NET code with the RS utility.
• Run the report again to verify that it was not retrieved from the cache.

Create a new report or pick an existing report and add the following Dataset to the report:

Use as a DataSource any SQL Server database.  We're just invoking the GETDATE() function which returns the current date and time.

Add a Texbox to the report; drag and drop the TIMESTAMP column from the Dataset created above onto the Textbox:

Deploy the report to your report server by right clicking the SSRS project in the Solution Explorer and selecting Deploy from the context menu.

To setup caching for the report, run the report using the Report Manager web application.  Click the Properties tab then click Execution.  You will see the following cache options:

Select either of the cache options (expire after a number of minutes or expire based on a schedule).  Run the report again and note the date and time displayed at the bottom:

Rerun the report several times and you will see that the date and time doesn't change; this means that the report is being rendered from the cache.  You can click the Refresh icon on the toolbar to rerun the report (it's between Export and the Printer icon).

Open a Command Prompt and execute the RS command to remove the report from the cache:

You should see the output "The command completed successfully".  Run the report one more time and you should see that the data and time have changed.  The report was not rendered from the cache; it was executed and a new data and time will be shown.

Next Steps

• Download the sample code and try out removing a report from cache.  The sample code was tested using SQL Server 2005 Developer Edition with SP2.
• There are many other administrative tasks that can be performed on SSRS using RS and VB.NET code; take a look at the Report Server Web Service topic in Books on Line and review the web service methods available.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips