SSRS - Reportserver Database Tables Explored - Part 2

It has been a long gap between my first post and this one. It has been a very tight schedule in the project i have been assigned to.At last got some breathing time to continue the series.

Today let us look into the below tables

1. Subscription
2. Schedule
3. ReportSchedule
4. ActiveSubscription
5. Notifications
6. Event
7. Batch

Subscription

This table stores the subscriptions created by the user.

Some important fields

Description           - Name or detail of the subscription
Laststatus             - Last status of message of the subscription
Eventype              - Event type which has used the subscription
Parameters           - XML value with the parameters used while running the subscription
DeliveryExtension - The extension to which the report is delivered

Schedule

Stores the details of the schedules like shared schedules , TimedSubscription , ReportHistorySchedule

Some important fields

Name                    - Schedule Name
StartDate              - Schedule StartDate
NextRunTime       - Next run time for the schedule
LastRunTime        - Last Run time of the schedule
Endate                 - The end date for the schedule
State                    - State of the Subscription. If state > 2 , the subscription is expired i.e, endate has crossed.
RecurrenceType   - Stores the recurrencetype of the schedule
                              1 - Once
                              2 - Hourly
                              4 - Daily / Weekly
                              6 - Monthly
EventType           - Stores the eventtype
                              SharedSchedule - Shared schedule
                              TimedSubscription - Schedule created within the subscription
                              RefreshCache - Schedule created for refreshing the cache.

ReportSchedule

Stores the mapping between Schedule , Report & Subscription.

ActiveSubscriptions

Stores the subscription notification consolidated results.

Notifications

Stores the notification sent by the subscriptions.

Event

Internal Table. When the scheduled time comes for a scheduler , the SQL Server Agent generates an event by executing the scheduled job. The job inserts a row in the Event table of the ReportServer database. This row serves as an event for the Scheduling and Delivery Processor. The event record will be deleted after the process.

Batch

Internal Table. One event will be assigned as batches and processed. The batch record also will be deleted after process.

Sample usage queries

1. Get the Schedule List with type and Recurrence

SELECT Name
    ,StartDate
    ,EndDate
    ,NextRunTime
    ,LastRunTime
    ,LastRunStatus
    ,RecurrenceType = CASE RecurrenceType
            WHEN  1 THEN 'Once'
            WHEN  2 THEN 'Hourly '
            WHEN  4 THEN 'Daily / Weekly'
            WHEN  6 THEN 'Monthly'
            End
   ,EventType
FROM Schedule

2. Query to get the list of Subscription and it's schedule for a given report

SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,su.Parameters
      ,Schedulename=sch.Name
      ,sch.Type
      ,sch.EventType
  FROM Subscriptions su
  JOIN Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
 WHERE c.Name = '@ReportName'

3. Query to get the notification details sent for the given report

SELECT C.Name
      ,S.Description
      ,N.NotificationEntered
      ,A.TotalNotifications
      ,A.TotalSuccesses
      ,A.TotalFailures
  FROM Notifications N
  JOIN ActiveSubscriptions A
    ON N.SubscriptionID = A.SubscriptionID
   AND N.ActivationID = A.ActiveID
  JOIN Catalog C
    ON C.ItemID = N.ReportID
  JOIN Subscriptions S
    ON S.SubscriptionID = N.SubscriptionID
 WHERE c.Name = '@ReportName'