>

ssrs를 처음 사용합니다.

RessportServer 데이터베이스에서 사용 가능한 ssrs 구독 보고서에 대해 가능한 모든 데이터를 가져오고 싶습니다.

일부 검색어를 찾았지만 적절한 데이터가 없습니다. 단일 보고서에서만 작동합니다.

데이터가 포함 된 고유 한 구독 목록이 필요합니다. 가능하면 저장 프로 시저가 바람직합니다.

내 질문 :

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'

미리 감사합니다.

  • 답변 # 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

    여전히 쿼리가 있으면 주석 처리하십시오 ..

  • 답변 # 2

    SQL 서버 에이전트 작업을 찾아야 할 경우이 업데이트 된 코드를 사용하십시오

    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];
    
    
    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]), j.name AS SQLServerAgentJob, 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 LEFT JOIN msdb.dbo.sysobjects so ON rs.ScheduleID= so.name INNER JOIN msdb.dbo.sysjobs J ON CONVERT( NVARCHAR(128), RS.ScheduleID ) = J.name INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

관련 자료

  • 이전 postgresql - postgres 쿼리를 실행할 때 유효하지 않은 메모리 주소 오류
  • 다음 regex - 특정 단어를 포함하지 않으면 문자열 일치