February 14, 2013 at 1:58 am
Hi there,
I have a few reports that I cached as they load a lot of data that doesn't change all day long.
I created a scheduled to expire the cache.
So far so good...
As my DWH is depending on a lot of other systems the jobs to process the data for the reports do not finish on the exact same time.
However I would like to avoid to schedule the expiration of the cache with a time buffer to be sure the data is ready of the report.
Instead I would prefer to trigger the expiration through the Agent-Job that is creating the data. It nows best, when its ready 😉
Is there a way to expire the cache/force to refresh through an SQL statement/or a procedure call?
Mitch
February 14, 2013 at 2:06 am
Does this help? Would just be a case of calling the job...
http://msdn.microsoft.com/en-us/library/ee636367%28v=sql.105%29.aspx
February 14, 2013 at 2:25 am
Hi anthony,
This just seems to be the description of the user interface and how you can create/edit/delete cache-plans.
I didn't find anything here that tells you how to do it with SQL
cheers,
mitch
February 14, 2013 at 2:29 am
mitch.fh (2/14/2013)
Hi anthony,This just seems to be the description of the user interface and how you can create/edit/delete cache-plans.
I didn't find anything here that tells you how to do it with SQL
cheers,
mitch
To open the Cache Refresh Plan properties page for a report or shared dataset
Open Report Manager, and locate the report or shared dataset for which you want to configure cache refresh plan properties.
Hover over the report or shared dataset, and click the drop-down arrow.
In the drop-down list, click Manage. The General properties page opens.
Click the Cache Refresh Plan tab.
To create a new cache plan, click New Cache Refresh Plan.
NoteNote
You must enable and start the SQL Server Agent service to create a cache refresh plan.
To create a copy of a cache plan and then customize it, click New from Existing.
Would of thought from this that it creates a job as it requires the Agent, then would just be a case of finding the job and running it when you need to refresh the cache.
February 14, 2013 at 2:39 am
I see were you are going.
That is actually not bad 🙂
I will give it a try and will report back here, if it is working
February 18, 2013 at 7:12 am
so I tried the AddEvent SP out. It works fine.
However I read somewhere that this undocumented procedure might be a deprecated feature of SQL Server. (still available in my SQL Server 2012 SSRS)
So you should not get too attached to it.
meanwhile...
I would like to use this e.g. in my SQL Agent Jobs.
For this I need to mimimze my maintenance task, as I am not always around when someone is modifing the cache configurations (e.g. deleting one cache config and creating a new one), I don't know if the EventData-IDs might have changed.
So I created a little procedure that fetches all the subscriptions of an desired type (e.g. RefreshCache) and creates an AddEvent-Call for it.
I kept it simple, but feel free to put more effort into it. You can probably filter subscriptions that are still active and so on...
As it is encapsulated there is a change to modify what is triggered one's M$ replaces the AddEvent-Procedure with something new.
Have fun
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Christian Terhart
-- Create date: 2013-02-18
-- Description:Trigger Report Scheduled Task
-- =============================================
ALTER PROCEDURE dbo.usp_report_event_trigger
@report_name nvarchar(425) = NULL,
@report_path nvarchar(425) = '',
@report_action int = -1
/*ReportActionEventType
3CacheInvalidateSchedule
4TimedSubscription
5RefreshCache
*/
AS
BEGIN
SET NOCOUNT ON;
DECLARE @exec_command nvarchar(4000) = '';
SELECT
@exec_command =
@exec_command + N'EXECUTE ReportServer.dbo.AddEvent @EventType=''' + sc.EventType + ''', @EventData='''+CONVERT(nvarchar(36), UPPER(rs.SubscriptionID))+N'''; '
FROM dbo.Catalog AS c
INNER JOIN dbo.ReportScheduleAS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
INNER JOIN dbo.ScheduleAS sc ON (rs.ScheduleID = sc.ScheduleID)
WHERE c.Name = @report_name
AND (c.Path = @report_path OR @report_path = '')
AND (rs.ReportAction = @report_action OR @report_action = -1)
EXECUTE sp_executesql @exec_command
END
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply