title: SSRS Subscriptions - How to view ALL report recipients
body: <p>I've written an SSRS report to help me keep track of SSRS subscriptions.  I've repurposed a script that will use Reportserver.dbo.Subscriptions.LastStatus to view email recipients, however, it will only list the first 520 characters of LastStatus.  Because some of our distribution lists are quite large, some of the names that my script searches for are not being found (even though they are part of the distribution).  Below is the script that I am using:</p>
  
  <pre><code>SELECT Reportname = c.Name 
    ,FileLocation = c.Path
    ,SubscriptionDesc=su.Description 
    ,Subscriptiontype=su.EventType 
    ,su.LastStatus 
    ,su.LastRunTime 
    ,Schedulename=sch.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' 
     END 
    ,su.Parameters 
    FROM Reportserver.dbo.Subscriptions su 
    JOIN Reportserver.dbo.Catalog c 
      ON su.Report_OID = c.ItemID 
    JOIN Reportserver.dbo.ReportSchedule rsc 
      ON rsc.ReportID = c.ItemID 
     AND rsc.SubscriptionID = su.SubscriptionID 
    JOIN Reportserver.dbo.Schedule Sch 
      ON rsc.ScheduleID = sch.ScheduleID 
  WHERE  LastStatus like @Email
  ORDER BY LastRunTime DESC
  </code></pre>
  
  <p>Any code that I have found online uses the LastStatus column to display this data.  If anyone has any suggestions as to a more complete way for me to list all of the members of the report distribution list, I would appreciate it.</p>