如何从SSRS的Subscriptions表ExtensionSettings字段提取所有路径

以下脚本直接运行在SSRS对应的ReportServer数据库即可实现需求，默认提取文件共享订阅的存储路径：

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices')
SELECT 
    s.SubscriptionID,
    c.Name AS ReportName,
    u.UserName AS SubscriptionOwner,
    ParamValues.PathValue AS ExportPath
FROM 
    [ReportServer].[dbo].[Subscriptions] s
JOIN 
    [ReportServer].[dbo].[Catalog] c ON s.Report_OID = c.ItemID
JOIN 
    [ReportServer].[dbo].[Users] u ON s.OwnerID = u.UserID
CROSS APPLY 
    s.ExtensionSettings.nodes('/ParameterValues/ParameterValue') AS Params(ParamNode)
CROSS APPLY 
    (SELECT 
        ParamNode.value('(Name)[1]', 'varchar(255)') AS ParamName,
        ParamNode.value('(Value)[1]', 'varchar(1000)') AS PathValue
    ) AS ParamValues
WHERE 
    ParamValues.ParamName = 'PATH'
    AND ParamValues.PathValue IS NOT NULL
ORDER BY 
    c.Name, u.UserName

脚本调整说明 #

• 如果你需要提取其他配置项，比如邮件收件人地址，把WHERE条件里的ParamName = 'PATH'改成ParamName = 'TO'即可
• 如果你的SSRS版本对应的ExtensionSettings命名空间不一致，可以先执行SELECT TOP 1 ExtensionSettings FROM dbo.Subscriptions查看根节点的命名空间，替换脚本开头XMLNAMESPACES里的地址即可
• 如果ExtensionSettings字段存储的是字符串格式而不是XML格式，可以把CROSS APPLY中的s.ExtensionSettings替换为CAST(s.ExtensionSettings AS XML)即可

内容的提问来源于stack exchange，提问作者Erik