Executing Subscriptions via Stored Procedure in SQL Reporting Services 2005
Author: jason
Date: 2008-10-02 02:23:22
Category: Technical

SQL Reporting Services allows users and administrators to set up timed subscriptions to be saved to a shared folder or emailed out. I find this a very useful feature. What I find more useful is being able to execute all the subscriptions whenever I want to, maybe I donít want to wait until the next scheduled time.

One way to run all the subscriptions at roughly the same time is by creating records for each subscribed report in the ReportServer Event table.

Below is a stored procedure to step through each report in the Schedule table and use the built-in AddEvent stored procedure to place the EventData into the Event table.

sp_runreports

set nocount on
declare cc cursor fast_forward
for select EventData from ReportServer.dbo.Schedule
declare @Event nvarchar(255)
open cc
fetch next from cc into @Event
while @@fetch_status=0
begin
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@Event
fetch next from cc into @Event
end
close cc
deallocate cc


The sp_AddEvent takes the EventData and creates a row in the Event table

sp_AddEvent (built-in)

@EventType nvarchar (260)
@EventData nvarchar (260)
insert into [Event]
([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])
values
(NewID(), @EventType, @EventData, GETUTCDATE(), NULL, NULL)


The Event table is constantly monitored by Reporting Services for jobs to execute.



The Schedule table contains information concerning each timed subscriptions, things like: StartDate, LastRunTime, EventType, etc... We use this table to get the EventData field to put into the Event table for execution.



If you create the sp_runreports procedure and run it, it will get the EventData field from each row in Schedule table and call the sp_AddEvent procedure to add the event to the Event table. Then the Event table will look like this, for about a minute.



After about 30-60 seconds the subscription reports should run and clear out of the Event table.




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009