How to get subscription associated with the reports on Report Server

SELECT Reportname = c.Name
      ,ScheduleType = sch.EventType
      ,ScheduleFrequency =
       CASE sch.RecurrenceType
       WHEN 1 THEN ‘Once’
       WHEN 2 THEN ‘Hourly’
       WHEN 4 THEN ‘Daily/Weekly’
       WHEN 5 THEN ‘Monthly’
  FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
  JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s