SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Triggering" refresh Report-Cache by SQL


"Triggering" refresh Report-Cache by SQL

Author
Message
christian_t
christian_t
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 530
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
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10037 Visits: 6303
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


christian_t
christian_t
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 530
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
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10037 Visits: 6303
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


christian_t
christian_t
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 530
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
christian_t
christian_t
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 530
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
/* ReportAction EventType
3 CacheInvalidateSchedule
4 TimedSubscription
5 RefreshCache
*/
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.ReportSchedule AS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
INNER JOIN dbo.Schedule AS 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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search