InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 12:08:45 GMT20http://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspxso 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 [b]too attached[/b] 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[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 */ASBEGIN 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 ENDGO[/code]Mon, 18 Feb 2013 07:12:21 GMTchristian.terharthttp://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspxI see were you are going.That is actually not bad :-)I will give it a try and will report back here, if it is workingThu, 14 Feb 2013 02:39:43 GMTchristian.terharthttp://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspx[quote][b]mitch.fh (2/14/2013)[/b][hr]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 SQLcheers,mitch[/quote][quote]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. [/quote]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.Thu, 14 Feb 2013 02:29:17 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspxHi 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 SQLcheers,mitchThu, 14 Feb 2013 02:25:15 GMTchristian.terharthttp://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspxDoes this help? Would just be a case of calling the job...[url]http://msdn.microsoft.com/en-us/library/ee636367%28v=sql.105%29.aspx[/url]Thu, 14 Feb 2013 02:06:50 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1419873-1632-1.aspxHi 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?MitchThu, 14 Feb 2013 01:58:26 GMTchristian.terhart