CODEWITHSUNDEEP
sqlreporting-services
Avinash Jha
Avinash Jha

Reputation: 1

How to create new snapshot in SSRS report by SQL Query for particular report

It is easy to create snapshot for report on report server by click on new snapshot button through User interface, but i need to create new snapshot by using only SQL Query. It means what will be the SQL query by which i can create new snapshot for particular report on report server without using User interface? I

Upvotes: 0

Views: 1365

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

Hitting the ReportServer database directly is discouraged. The preferred method for generating an SSRS report snapshot is to invoke the CreateReportHistorySnapshot API method with C#. However, if you must use SQL then have a look at the AddEvent ReportServer stored procedure. 

exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='<InsertReportIDHere>'

More information can be found here and here. Below is a sample SQL script which can generate a new Snapshot for a given report on a daily basis. 

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

Upvotes: 1

Related Questions