CODEWITHSUNDEEP
sql-serverreporting-services
Geezer
Geezer

Reputation: 497

SSRS Subscriptions table and ExecutionLog3 View

We have many reports that have multiple subscriptions and I want to be able to monitor the executions by querying the Subscriptions table and ExecutionLog3 View.

There doesn't seem to be an obvious relationship between the table and view.

One particular report has one data driven subscription. The report is executed multiple times with a different parameter value each time. I particularly want to monitor the progress of this subscription. I want to be able to identify when it fails, when, and which parameter was used.

I came across a possible solution here but he accepted answer is wrong - see comments

Upvotes: 1

Views: 3362

Answers (1)

SS_DBA
SS_DBA

Reputation: 2423

See if this works for you... I've been using this for quite some time.

    SELECT USR.UserName COLLATE Latin1_General_100_CI_AS_KS_WS AS SubscriptionOwner 
          ,SUB.ModifiedDate  as ModifiedDate
          ,SUB.[Description] COLLATE Latin1_General_100_CI_AS_KS_WS As Description
          ,SUB.EventType COLLATE Latin1_General_100_CI_AS_KS_WS as EventType
          ,SUB.DeliveryExtension COLLATE Latin1_General_100_CI_AS_KS_WS As DeliveryExtension
          ,SUB.LastStatus COLLATE Latin1_General_100_CI_AS_KS_WS as LastStatus
          ,SUB.LastRunTime  As LastRunTime
          ,SCH.NextRunTime  As NextRunTime
          ,SCH.Name    COLLATE Latin1_General_100_CI_AS_KS_WS    AS ScheduleName
          ,CAT.[Path]  COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportPath
          ,CAT.[Description] COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportDescription
          ,CAT.Name COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportName
    FROM ReportServer.dbo.Subscriptions AS SUB 
         INNER JOIN ReportServer.dbo.Users AS USR 
             ON SUB.OwnerID = USR.UserID 
         INNER JOIN ReportServer.dbo.[Catalog] AS CAT 
             ON SUB.Report_OID = CAT.ItemID 
         INNER JOIN ReportServer.dbo.ReportSchedule AS RS 
             ON SUB.Report_OID = RS.ReportID 
                AND SUB.SubscriptionID = RS.SubscriptionID 
         INNER JOIN ReportServer.dbo.Schedule AS SCH 
             ON RS.ScheduleID = SCH.ScheduleID 
         INNER JOIN ReportServer.dbo.ExecutionLog3 EX 
            ON Cat.Path = EX.ItemPath AND EX.RequestType = 'Subscription'
    ORDER BY 1,10

Upvotes: 3

Related Questions