Applies To: Tested on SQL Server Reporting Services 2008 and 2008 R2.
In this blog, I would like to discuss about one of the known issues that you might have encountered in Reporting Services 2008 R2. If you have created a data driven subscription to get the cached data and along with that if you have an expiry schedule set, then you will notice that even if the data driven subscription runs fine, you won't get the cached data for the report.
Scenario:
Let's say you have created a data driven subscription for a report and selected a NULL delivery provider to get the cached data. Once the data driven subscription is set, under Manage Report -> Processing Options you can set the time or a schedule until which the cached report will last. Use the image below for reference:
If you select the second option for Report caching, you can assign a time(in minutes) for the cache expiry. If the third option is chosen, you need to create a schedule or use a shared schedule for the cache expiry. A shared schedule can be created from the Site settings-> Schedules-> New schedule.
Use the image below for reference:
The image below is a schedule that is configured to run once.
Once the schedule is created you can use the shared schedule in the Subscription.
Upon executing the subscription, you will notice that the data rendered in the report is Live and not Cached. To be precise, the ExecutionLog3 view of the report server database will have the “Source” field set to LIVE instead of CACHE. Use the image below for reference:
Issue:
Whenever a shared expiry schedule is configured to run once, it should be deactivated post the scheduled time. But the issue here is if the same expiry schedule is still mapped to the same subscription, further when the subscription runs, it won't take the cache data thinking that the expiry schedule is active.
Resolution:
Well the resolution is quite simple. If you are creating a schedule with one time execution then make sure that once the schedule is executed, go ahead, and delete it or recreate it same schedule with a newer date.
Takeaway from this Blog:
We can easily figure out from this blog that whenever you create a data driven subscription to get a cached report make sure that the expiry schedule is not set to run for just one time. Instead of that you can schedule the expiry schedule as well that would run daily before the data driven subscription. In this way, you can make sure that before the data driven subscription runs the older cached data is deleted and after the subscription runs you will get a new set of cached data.
Author: Jaideep Saha Roy – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft