Today morning, I was reading an old post on Clearing SSRS Query cache by Alex Whittles (blog | twitter). That is when I decided to try out something and opened up my most recent project to find some rdl.data files. Surprisingly, I did not find a single rdl.data file although I had more than 10 reports which had been run multiple times. Even a full search of my laptop failed to yield results for those elusive files. That is when I decided to investigate further into the matter.
The rdl.data files are usually present for each report and are used only in the development environment. When we preview the SQL Server Reporting Services (SSRS) reports in Business Intelligence Development Studio (BIDS – the development environment), the query results are cached and stored as the rdl.data files in the report folder. This cache will be used the next time the reports are previewed and is the reason why the report seems to display faster. This will help in developing reports faster as we don’t have to wait a long time for the report to be displayed. However, this could also mean that the data would be stale when we are previewing the report in BIDS.
Now, coming back to the issue, I quickly noted my version of SQL Server (which is 2008 R2) and checked for updates. I realized that I had not applied the Service Pack 1 for SQL Server 2008 R2 and quickly searched the internet for a bug, which I found here. So all I had to do was to apply the Service Pack 1 which I downloaded from this link. Once the Service Pack was applied, things got back to normal and I was able to generate the rdl.data files when the reports were previewed.
P.S. : Kindly note the version to which this fix applies – Microsoft SQL Server 2008 R2 Reporting Services (without any patches/updates/service packs)
Tags: SQL Server, #TSQL, #SQLServer,