Execute Subscription through SQL Query

In one scenario I needed to execute a subscription of SSRS report manually so for this I did some R&D to find out the way of execution of subscriptions in SSRS.

So for Data Driven Subscription, report server uses a table Event from report server database to trigger a subscription and for standard subscription, report server uses Notification table from report server database.
So I created a SQL job where I pulled the subscription details from Subscriptions table of report server database i.e. SubscriptionID and designed an insert statement for inserting information into the Event/Notification table.
For getting Subscription related information (like report name, parameter information, Type of subscription, Last Run Time, Extension settings and Data Settings etc.. ) we can use report server database objects to querying data from them:
Below are some important tables of Report server which are keeping subscription related information:

·         Subscriptions

·         ReportSchedule

·         Schedule

·         Catalog

Ø  Below query returns all infromation related to a subscriptions:

Select

C.Name as ReportName

,Sub.Description

,C.Path  ReportPath

,Sub.SubscriptionID

,Sub.LastRunTime

,Sub.LastStatus

,Convert(xml,Sub.ExtensionSettings) as ExtensionSettings --- Email list, render format, Subject, include report or link information

,Convert(xml,Sub.Parameters) as Parameter ---- Parameter Page infromation

,Convert(xml,Sub.DataSettings) as Datasettings  ---- Query Page information

,Sch.Name as ScheduleName

From ReportServer..Subscriptions Sub WITH(NOLOCK)

      Left outer join ReportServer..ReportSchedule RSch WITH(NOLOCK) on Sub.SubscriptionID=RSch.SubscriptionID

      Left outer join ReportServer..Schedule Sch WITH(NOLOCK) on RSch.ScheduleID=Sch.ScheduleID

       Left outer join ReportServer..Catalog  C WITH(NOLOCK) on SUB.Report_OID = C.ItemID

Ø  Below Insert query is an example to trigger Data Driven Subscription through Event Table:

       

     Insert into Reportserver..event(EventID,EventType,EventData,TimeEntered)

            values(NEWID(),'TimedSubscription','F005ADD5-C54C-4028-A755-01DFEE7524AB',GETDATE()) 

    

Ø  Below Insert query is an example to trigger Standard Subscription through Notification Table:

    

      Insert into ReportServer..Notifications

      (         NotificationID,SubscriptionID,ReportID,ExtensionSettings,Locale,NotificationEntered,

                        SubscriptionLastRunTime,DeliveryExtension,SubscriptionOwnerID,IsDataDriven,Version,ReportZone, Parameters

                  )

                        Select NEWID()as NotificationID,SubscriptionID,Report_OID,ExtensionSettings,Locale,getdate()

                        ,Isnull(LastRunTime,Dateadd(dd,-1,GETDATE())) ,DeliveryExtension ,OwnerID

                        ,Case when DataSettings is null then 0 else 1 end as IsDatadriven ---for DataDriven

                        ,Version,ReportZone  , Parameters

                        from ReportServer..Subscriptions WITH(NOLOCK)

                        where SubscriptionID ='A2D36090-1021-4DBB-AE6A-12A19730C89D'

                                    and DataSettings is null

Here, in the insert statement, column EventData is SubscriptionID from Subscriptions table and EventID is a guid ID which I generated by using NEWID() function (NEWID() is based on a combination of a pseudorandom number (from the clock) and the MAC address of the primary NIC. However, inserting random numbers like this as the clustered key on a table is terrible for performance. You should consider either NEWSEQUENTIALID() or a COMB-type function for generating GUIDs that still offer the collision-avoidance benefits of NEWID() while still maintaining acceptable INSERT performance.).

So these tables (Event and Notification) are worked like a trigger table so whenever a row is inserted with required information then an inbuilt job is called to trigger subscriptions which are mentioned in the inserted rows of the Event and Notification table.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!