问正在传递给订阅服务器的SSRS报告的重复副本

EN

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 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, 
    Report_OID, LastRunTime, LastStatus, InactiveFlags
FROM
subscriptionXmL
CROSS APPLY subscriptionXmL.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries ( Settings ))

select distinct 
ssrs.name,
ssrs.Path,
--convert(varchar(max),ReportQry) ReportQry,
[LastExec] [SQL Last Executed],
subs.SettingName,
subs.SettingValue [Recipients],
InactiveFlags
from
(SELECT top (100) percent Name,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportQry,
ItemID [Report_ID],
Path
FROM dbo.Catalog c
) ssrs
    -- grab the last executed date
    left join  (select max(TimeStart) [LastExec], ReportID from ExecutionLog ex group by ReportID)ex
    on ssrs.[Report_ID] = ex.ReportID

    left join (
            SELECT *
            FROM
                (select dbo.Catalog.Name, SettingsCTE.SubscriptionID, SettingsCTE.SettingName, SettingValue, LastRunTime, LastStatus, Report_OID, InactiveFlags
                FROM
                SettingsCTE JOIN dbo.Catalog on SettingsCTE.Report_OID = dbo.Catalog.ItemID
                WHERE
                settingName IN ( 'TO', 'CC', 'BCC' )
                AND SettingValue IS NOT NULL
            ) subs
            )subs
        on ssrs.Report_ID = subs.Report_OID
 where 
 -- filter down to a report that references a particular object name
 (convert(varchar(max),ReportQry) LIKE '%you object / table / view%')
 -- or filter down to an individual report
 OR ssrs.name like '%your repot name%'