reporting-services - SSRS订阅-如何查看所有报告收件人

Question

我写了一个SSRS报告来帮助我跟踪SSRS订阅。我已经重新调整了脚本的用途，该脚本将使用Reportserver.dbo.Subscriptions.LastStatus查看电子邮件收件人，但是，它将仅列出LastStatus的前520个字符。因为我们的某些通讯组列表很大，所以找不到我的脚本搜索的某些名称（即使它们是通讯组的一部分）。以下是我正在使用的脚本：

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

我在网上找到的任何代码都使用LastStatus列显示此数据。如果有人对我列出报告分发列表的所有成员的更完整方法有任何建议，我将不胜感激。

Answer 1

下面是查询订阅参数全文的SQL。我认为这将适用于非常长的地址列表，但是我现在没有具有长地址列表的测试服务器。

如果在生产中使用它，我可能会抛出几个WITH ( NOLOCK )，并且不会期望MS在问题上提供支持。

   ;
   WITH subscriptionXmL
          AS (
               SELECT
                SubscriptionID ,
                OwnerID ,
                Report_OID ,
                Locale ,
                InactiveFlags ,
                ExtensionSettings ,
                CONVERT(XML, ExtensionSettings) AS ExtensionSettingsXML ,
                ModifiedByID ,
                ModifiedDate ,
                Description ,
                LastStatus ,
                EventType ,
                MatchData ,
                LastRunTime ,
                Parameters ,
                DeliveryExtension ,
                Version
               FROM
                ReportServer.dbo.Subscriptions
             ),
                 -- Get the settings as pairs
        SettingsCTE
          AS (
               SELECT
                SubscriptionID ,
                ExtensionSettings ,
    -- include other fields if you need them.
                ISNULL(Settings.value('(./*:Name/text())[1]', 'nvarchar(1024)'),
                       'Value') AS SettingName ,
                Settings.value('(./*:Value/text())[1]', 'nvarchar(max)') AS SettingValue
               FROM
                subscriptionXmL
                CROSS APPLY subscriptionXmL.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries ( Settings )
             )
    SELECT
        *
    FROM
        SettingsCTE
    WHERE
        settingName IN ( 'TO', 'CC', 'BCC' )