Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Triggering" refresh Report-Cache by SQL Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 1:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:55 AM
Points: 97, Visits: 431
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

Post #1419873
Posted Thursday, February 14, 2013 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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
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

Post #1419878
Posted Thursday, February 14, 2013 2:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:55 AM
Points: 97, Visits: 431
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
Post #1419888
Posted Thursday, February 14, 2013 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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
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

Post #1419892
Posted Thursday, February 14, 2013 2:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:55 AM
Points: 97, Visits: 431
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
Post #1419895
Posted Monday, February 18, 2013 7:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:55 AM
Points: 97, Visits: 431
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


Post #1421207
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse