sql - 如何使用存储过程获取所有 SSRS 订阅数据？

Question

我是 ssrs 的新手。

我想获取 ssrs 订阅报告的所有可能数据，这些数据在 ResportServer 数据库中可用。

我发现了一些查询，但没有正确的数据。它仅适用于单个报告。

我需要唯一订阅列表及其数据。如果可能，最好使用存储过程。

我的查询:

SELECT
        b.name AS JobName
        , e.name
        , e.path
        , d.description
        , a.SubscriptionID
        , laststatus
        , eventtype
        , LastRunTime
        , date_created
        , date_modified
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b
        ON a.ScheduleID = b.name
JOIN ReportServer.dbo.ReportSchedule c
        ON b.name = c.ScheduleID
JOIN ReportServer.dbo.Subscriptions d
        ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e
        ON d.report_oid = e.itemid
WHERE e.name = 'Sales_Report'

提前致谢。

Answer 1

我曾经有过和你现在一样的需求...

请参阅下面的存储过程..

CREATE PROCEDURE [dbo].[GetSubscriptionData]
AS
BEGIN
SET NOCOUNT ON;
WITH
[Sub_Parameters] AS
(
    SELECT  [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters])
    FROM [Subscriptions] a
),
[MySubscriptions] AS
(
    SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),   [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
    FROM [Sub_Parameters] a
        CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
    SELECT  a.[SubscriptionID], a.[ParameterName],  [ParameterValue] =  
        (
            SELECT  STUFF((SELECT [ParameterValue] + ', ' as [text()] 
            FROM [MySubscriptions]  
            WHERE   [SubscriptionID] = a.[SubscriptionID]   AND [ParameterName] = a.[ParameterName] 
                FOR XML PATH('')    ),1, 0, '') +''
        )
    FROM [MySubscriptions] a
    GROUP BY a.[SubscriptionID],a.[ParameterName]
)

SELECT
DISTINCT (a.[SubscriptionID]),
c.[UserName] AS Owner, 
b.Name,
b.Path,
a.[Locale], 
a.[InactiveFlags], 
d.[UserName] AS Modified_by, 
a.[ModifiedDate], 
a.[Description], 
a.[LastStatus], 
a.[EventType], 
a.[LastRunTime], 
a.[DeliveryExtension],
a.[Version],
sch.StartDate,
--e.[ParameterName],
--LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName
FROM 
    [Subscriptions] a 
    INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID]
    Inner Join ReportSchedule as RS on rs.SubscriptionID = a.SubscriptionID
    INNER JOIN Schedule AS Sch ON Sch.ScheduleID = rs.ScheduleID
    LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID]
    LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid
    LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID;
END

This is simplified query to get all SSRS Subscriptions

SELECT USR.UserName AS SubscriptionOwner 
  ,SUB.ModifiedDate 
  ,SUB.[Description] 
  ,SUB.EventType 
  ,SUB.DeliveryExtension 
  ,SUB.LastStatus 
  ,SUB.LastRunTime 
  ,SCH.NextRunTime 
  ,SCH.Name AS ScheduleName       
  ,CAT.[Path] AS ReportPath 
  ,CAT.[Description] AS ReportDescription 
FROM dbo.Subscriptions AS SUB 
 INNER JOIN dbo.Users AS USR 
     ON SUB.OwnerID = USR.UserID 
 INNER JOIN dbo.[Catalog] AS CAT 
     ON SUB.Report_OID = CAT.ItemID 
 INNER JOIN dbo.ReportSchedule AS RS 
     ON SUB.Report_OID = RS.ReportID 
        AND SUB.SubscriptionID = RS.SubscriptionID 
 INNER JOIN dbo.Schedule AS SCH 
     ON RS.ScheduleID = SCH.ScheduleID 
ORDER BY USR.UserName, CAT.[Path];

如果您还有任何疑问，请评论..