March 14, 2008 at 11:48 pm
Hi everyone,
I need a help on creating a snapshot of a report on a daily bases as a step in a SQL Server job. It means, my report renders from a snapshot and I want that snapshot updated with the most recent data every day right after an ETL process finishes data transformation.
I've tried with making an initial scheduled report rendered as a snapshot which will be later used as a base for
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
but new event didn't result with snapshot updating. My linked report stays unchanged with a notification of "last run" the same as it was before adding an event.
Have you any suggestion on what should I do or what I am doing wrong?
March 17, 2008 at 6:20 pm
I had some progres myself and will report it to you right now, but still would like your opinion if anyone knows a better way of doing it.
The idea is to schedule a snapshot history and replace SnapshotDataID in the Catalog table with new snapshot ID every time an update is needed. Here is the code:
-- -------------------------------------------------------------------------------------------------------------------
declare @Path varchar(425)
set @Path = '/SSRS Testing and Training/Test_snapshot' -- the name of my linked report which renders from a snapshot
declare @EventData uniqueidentifier
select @EventData = (select ItemID from Catalog where Path = @Path)
-- make a new snapshot in History table
exec ReportServer.dbo.AddEvent 'ReportHistorySchedule', @EventData
-- !!!! wait until Reporting Services figures out that it has an event to process (it actually takes 5sec)
waitfor delay '00:00:10'
-- take a snapshot ID from a newly created row in History table
declare @SnapshotDataID uniqueidentifier
select @SnapshotDataID = (select SnapshotDataID from history WHERE ReportID = @EventData)
-- set a date for a new Snapshot in Catalog table
-- use getdate() instead (select SnapshotDate from history WHERE ReportID = @EventData) because otherwise you'll get a UTC date for "last run date" in Report Manager which can confuse your users
declare @SnapshotDate datetime
select @SnapshotDate = getdate()
-- run a RS stored procedure which updates SnapshotDataID in Catalog table and some other necessary things
exec UpdateSnapshot @Path,@SnapshotDataID,@SnapshotDate
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply