none
Report Schedule Meta Data RRS feed

  • Question

  • Is there a way to get to get the following information

    how many schedules run on a given day

    how many instances those schedules created (how many emails, ftps, fileshares etc...)

    what time the first started

    what time the last completed

    what time each started, stopped,

    time it took to run each schedule

    Thursday, October 2, 2014 5:42 PM

Answers

  • try this ;

    Make sure that you have already existing Schedule in Report server .

    You can try other tables in this report server DB like Execution Log,catalog and many more . you can check those tables as well ;

    I found ;

    http://stackoverflow.com/questions/8334642/ssrs-2008-r2-get-human-readable-schedule-information-from-reportserver-db

    WITH    EnhancedSched
          AS (
               SELECT
                    dbo.Schedule.ScheduleID ,
                    dbo.Schedule.Name ,
                    dbo.Schedule.StartDate ,
                    dbo.Schedule.Flags ,
                    dbo.Schedule.NextRunTime ,
                    dbo.Schedule.LastRunTime ,
                    dbo.Schedule.EndDate ,
                    dbo.Schedule.RecurrenceType ,
                    dbo.Schedule.MinutesInterval ,
                    dbo.Schedule.DaysInterval ,
                    dbo.Schedule.WeeksInterval ,
                    dbo.Schedule.DaysOfWeek ,
                    dbo.Schedule.DaysOfMonth ,
                    dbo.Schedule.Month ,
                    dbo.Schedule.MonthlyWeek ,
                    dbo.Schedule.State ,
                    dbo.Schedule.LastRunStatus ,
                    dbo.Schedule.ScheduledRunTimeout ,
                    dbo.Schedule.CreatedById ,
                    dbo.Schedule.EventType ,
                    dbo.Schedule.EventData ,
                    dbo.Schedule.Type ,
                    dbo.Schedule.ConsistancyCheck ,
                    dbo.Schedule.Path ,
                CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, '
                     ELSE ''
                END + CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, '
                           ELSE ''
                      END + CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, '
                                 ELSE ''
                            END + CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, '
                                       ELSE ''
                                  END
                + CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, '
                       ELSE ''
                  END + CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, '
                             ELSE ''
                        END + CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, '
                                   ELSE ''
                              END AS DaysOfWeekString ,
                CASE WHEN DaysOfMonth & 1 <> 0 THEN '1,'
                     ELSE ''
                END + CASE WHEN DaysOfMonth & 2 <> 0 THEN '2,'
                           ELSE ''
                      END + CASE WHEN DaysOfMonth & 4 <> 0 THEN '3,'
                                 ELSE ''
                            END + CASE WHEN DaysOfMonth & 8 <> 0 THEN '4,'
                                       ELSE ''
                                  END
                + CASE WHEN DaysOfMonth & 16 <> 0 THEN '5,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 32 <> 0 THEN '6,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 64 <> 0 THEN '7,'
                                   ELSE ''
                              END + CASE WHEN DaysOfMonth & 128 <> 0 THEN '8,'
                                         ELSE ''
                                    END
                + CASE WHEN DaysOfMonth & 256 <> 0 THEN '9,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 512 <> 0 THEN '10,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28,'
                             ELSE ''
                        END
                + CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30,'
                             ELSE ''
                        END AS DaysOfMonthString ,
                CASE WHEN Month = 4095 THEN 'every month, '
                     ELSE CASE WHEN Month & 1 <> 0 THEN 'Jan, '
                               ELSE ''
                          END + CASE WHEN Month & 2 <> 0 THEN 'Feb, '
                                     ELSE ''
                                END + CASE WHEN Month & 4 <> 0 THEN 'Mar, '
                                           ELSE ''
                                      END
                          + CASE WHEN Month & 8 <> 0 THEN 'Apr, '
                                 ELSE ''
                            END + CASE WHEN Month & 16 <> 0 THEN 'May, '
                                       ELSE ''
                                  END + CASE WHEN Month & 32 <> 0 THEN 'Jun, '
                                             ELSE ''
                                        END
                          + CASE WHEN Month & 64 <> 0 THEN 'Jul, '
                                 ELSE ''
                            END + CASE WHEN Month & 128 <> 0 THEN 'Aug, '
                                       ELSE ''
                                  END
                          + CASE WHEN Month & 256 <> 0 THEN 'Sep, '
                                 ELSE ''
                            END + CASE WHEN Month & 512 <> 0 THEN 'Oct, '
                                       ELSE ''
                                  END
                          + CASE WHEN Month & 1024 <> 0 THEN 'Nov, '
                                 ELSE ''
                            END + CASE WHEN Month & 2048 <> 0 THEN 'Dec, '
                                       ELSE ''
                                  END
                END AS MonthString ,
                CASE MonthlyWeek
                  WHEN 1 THEN 'first'
                  WHEN 2 THEN 'second'
                  WHEN 3 THEN 'third'
                  WHEN 4 THEN 'fourth'
                  WHEN 5 THEN 'last'
                END AS MonthlyWeekString ,
                ' starting ' + CONVERT (VARCHAR, StartDate, 101)
                + CASE WHEN EndDate IS NOT NULL
                       THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101)
                       ELSE ''
                  END AS StartEndString ,
                CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) + ':'
                + CASE WHEN DATEPART(minute, StartDate) < 10
                       THEN '0' + CONVERT(VARCHAR(2), DATEPART(minute,
                                                              StartDate))
                       ELSE CONVERT(VARCHAR(2), DATEPART(minute, StartDate))
                  END + CASE WHEN DATEPART(hour, StartDate) >= 12 THEN ' PM'
                             ELSE ' AM'
                        END AS StartTime
               FROM
                Schedule
             ),
        SuperEnhancedSchedule
          AS (
               SELECT
                EnhancedSched.ScheduleID ,
                EnhancedSched.Name ,
                EnhancedSched.StartDate ,
                EnhancedSched.Flags ,
                EnhancedSched.NextRunTime ,
                EnhancedSched.LastRunTime ,
                EnhancedSched.EndDate ,
                EnhancedSched.RecurrenceType ,
                EnhancedSched.MinutesInterval ,
                EnhancedSched.DaysInterval ,
                EnhancedSched.WeeksInterval ,
                EnhancedSched.DaysOfWeek ,
                EnhancedSched.DaysOfMonth ,
                EnhancedSched.Month ,
                EnhancedSched.MonthlyWeek ,
                EnhancedSched.State ,
                EnhancedSched.LastRunStatus ,
                EnhancedSched.ScheduledRunTimeout ,
                EnhancedSched.CreatedById ,
                EnhancedSched.EventType ,
                EnhancedSched.EventData ,
                EnhancedSched.Type ,
                EnhancedSched.ConsistancyCheck ,
                EnhancedSched.Path , -- spec what you need.
                CASE WHEN RecurrenceType = 1
                     THEN 'At ' + StartTime + ' on '
                          + CONVERT(VARCHAR, StartDate, 101)
                     WHEN RecurrenceType = 2
                     THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 ))
                          + ' hour(s) and '
                          + CONVERT(VARCHAR, ( MinutesInterval % 60 ))
                          + ' minute(s), ' + 'starting '
                          + CONVERT (VARCHAR, StartDate, 101) + ' at '
                          + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6)
                          + ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109),
                                            25, 2)
                          + CASE WHEN EndDate IS NOT NULL
                                 THEN ' and ending '
                                      + CONVERT (VARCHAR, EndDate, 101)
                                 ELSE ''
                            END
                     WHEN RecurrenceType = 3
                     THEN 'At ' + StartTime + ' every '
                          + CASE DaysInterval
                              WHEN 1 THEN 'day, '
                              ELSE CONVERT(VARCHAR, DaysInterval) + ' days, '
                            END + StartEndString
                     WHEN RecurrenceType = 4
                     THEN 'At ' + StartTime + ' every '
                          + CASE WHEN LEN(DaysOfWeekString) > 1
                                 THEN LEFT(DaysOfWeekString,
                                           LEN(DaysOfWeekString) - 1)
                                 ELSE ''
                            END + ' of every '
                          + CASE WHEN WeeksInterval = 1 THEN ' week,'
                                 ELSE CONVERT(VARCHAR, WeeksInterval)
                                      + ' weeks,'
                            END + StartEndString
                     WHEN RecurrenceType = 5
                     THEN 'At ' + StartTime + ' on day(s) '
                          + CASE WHEN LEN(DaysOfMonthString) > 1
                                 THEN LEFT(DaysOfMonthString,
                                           LEN(DaysOfMonthString) - 1)
                                 ELSE ''
                            END + ' of ' + MonthString + StartEndString
                     WHEN RecurrenceType = 6
                     THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString
                          + ' '
                          + CASE WHEN LEN(DaysOfWeekString) > 1
                                 THEN LEFT(DaysOfWeekString,
                                           LEN(DaysOfWeekString) - 1)
                                 ELSE ''
                            END + ' of ' + MonthString + StartEndString
                     ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0,
                                            6) + ' '
                          + SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2)
                          + StartEndString
                END ScheduleTextDefinition
                FROM EnhancedSched
             )
    SELECT
        *
        -- This has the same columns as the native [dbo].Schedule table plus a field called "SheduleTextDefinition"
        -- You can use "SuperEnhancedSchedule" in place of the usual SSRS.Schedule table, joining to subscriptions and such.
    FROM
        SuperEnhancedSchedule
    Thanks



    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem.


    Thursday, October 2, 2014 6:51 PM
  • Hi Pungigi,

    Based on your description, you  want to get all the Meta Data of the report Schedule, right?
    The related schedule information are in the tables of the ReportServer DB, please find the tables below:

    Subscriptions; 
    ReportSchedule;
    dbo.Schedule;
    dbo.ExecutionLog  ;    
    dbo.Catalog;
    sysjobsteps;
    dbo.sysjobschedules;
    dbo.Notifications;
    dbo.Event;

    I have tested on my local environment and you can take reference of the query provided by PrajapatiNeha  to get the Meta data of all the Schedules.

    If you want to get the schedules of all the subscriptions, you can execute query below:

    USE [ReportServer];   ---- You may change the database name. 
    GO 
    SELECT USR.UserName AS SubscriptionOwner 
          ,SCH.ScheduleID
          ,SCH.Name
          ,SCH.Flags 
          ,SCH.StartDate 
          ,SCH.LastRunTime 
          ,SCH.NextRunTime 
          ,SCH.EndDate 
          ,SCH.RecurrenceType 
          ,SCH.MinutesInterval 
          ,SCH.DaysInterval 
          ,SCH.WeeksInterval 
          ,SCH.DaysOfWeek 
          ,SCH.DaysOfMonth 
          ,SCH.Month 
          ,SCH.MonthlyWeek 
          ,SCH.State 
          ,SCH.LastRunStatus 
          ,SCH.ScheduledRunTimeout
          ,SCH.CreatedById 
          ,SCH.EventData 
          ,SCH.Type 
          ,SCH.ConsistancyCheck
          ,SCH.Path 
          ,SUB.SubscriptionID   
          ,SUB.ModifiedDate 
          ,SUB.[Description] 
          ,SUB.EventType 
          ,SUB.DeliveryExtension 
          ,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];

    You can find the details query about the question you are facing:

    1.  Get the Total numbers of Schedules, first start time and Last completed time on a given day

    select  count(ScheduleID) as TotalSchedule,
            min(StartDate) as FirstStart,
            Max(StartDate) as LastCompleted
    from dbo.Schedule
    where CONVERT (VARCHAR, StartDate, 101) like 'MM/DD/YYY';----09/01/2014

    2.  How many schedules of subscription created for each type(Email, FileShare)

    select COUNT(DeliveryExtension) as TotalDelivery from 
    (SELECT 
    SUB.DeliveryExtension 
    FROM dbo.Subscriptions AS SUB 
    INNER JOIN dbo.Users AS USR 
    ON SUB.OwnerID = USR.UserID 
    INNER JOIN dbo.[Catalog] AS CAT 
    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 ) a
    where DeliveryExtension like '%FileShare'
    --where DeliveryExtension like '%Email'

    Below links are for your reference about schedule and subscription:
    http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx
    http://stackoverflow.com/questions/8334642/ssrs-2008-r2-get-human-readable-schedule-information-from-reportserver-db

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu


    Tuesday, October 7, 2014 5:30 AM
    Moderator
  • Hi Pungigi,

    Base on your description that most of the tables don’t have any records, right?

    You can select  below tables one by one to check if all of the schedule related tables have no records:

    dbo.Subscriptions; 
    dbo.ReportSchedule;
    dbo.Schedule;
    dbo.ExecutionLog  ;    
    dbo.Catalog;
    dbo.sysjobs;
    dbo.sysjobsteps;
    dbo.sysjobschedules;
    dbo.Notifications;
    dbo.Event;

    When you create a subscription several things are added to the RS server:

    1. A row is placed in the dbo.Subscriptions table
    2. A row is placed in the dbo.Schedule and dbo.ReportSchedule tables
    3. A SQL Server Agent job is created(a row is placed in the dbo.sysjobs and dbo.sysjobsteps tables)

    So please check below first:

    • Did you scheduled any subscription on the given day?
      If you did ,you will have records in the tables of step1,2,3, If didn’t ,you will not have any records in the tables of step1 and step3.
    • Did you created any shared schedules ,Scheduled Cache or Scheduled  snapshot on the given day?
      If you did ,you will have records in the tables of step2

    If you have check above but still no records, The link about subscription troubleshooting are for your reference.
    http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx
    http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu

    Wednesday, October 8, 2014 6:34 AM
    Moderator

All replies

  • Hi ,

    All the report related information that are deploy to report can directly fetch from Report Server Database.

    and for report schedule information you can use ;

    select * from ReportSchedule

    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem.

    Thursday, October 2, 2014 6:41 PM
  • When I do that nothing comes back, the table is empty.

    Thursday, October 2, 2014 6:46 PM
  • try this ;

    Make sure that you have already existing Schedule in Report server .

    You can try other tables in this report server DB like Execution Log,catalog and many more . you can check those tables as well ;

    I found ;

    http://stackoverflow.com/questions/8334642/ssrs-2008-r2-get-human-readable-schedule-information-from-reportserver-db

    WITH    EnhancedSched
          AS (
               SELECT
                    dbo.Schedule.ScheduleID ,
                    dbo.Schedule.Name ,
                    dbo.Schedule.StartDate ,
                    dbo.Schedule.Flags ,
                    dbo.Schedule.NextRunTime ,
                    dbo.Schedule.LastRunTime ,
                    dbo.Schedule.EndDate ,
                    dbo.Schedule.RecurrenceType ,
                    dbo.Schedule.MinutesInterval ,
                    dbo.Schedule.DaysInterval ,
                    dbo.Schedule.WeeksInterval ,
                    dbo.Schedule.DaysOfWeek ,
                    dbo.Schedule.DaysOfMonth ,
                    dbo.Schedule.Month ,
                    dbo.Schedule.MonthlyWeek ,
                    dbo.Schedule.State ,
                    dbo.Schedule.LastRunStatus ,
                    dbo.Schedule.ScheduledRunTimeout ,
                    dbo.Schedule.CreatedById ,
                    dbo.Schedule.EventType ,
                    dbo.Schedule.EventData ,
                    dbo.Schedule.Type ,
                    dbo.Schedule.ConsistancyCheck ,
                    dbo.Schedule.Path ,
                CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, '
                     ELSE ''
                END + CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, '
                           ELSE ''
                      END + CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, '
                                 ELSE ''
                            END + CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, '
                                       ELSE ''
                                  END
                + CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, '
                       ELSE ''
                  END + CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, '
                             ELSE ''
                        END + CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, '
                                   ELSE ''
                              END AS DaysOfWeekString ,
                CASE WHEN DaysOfMonth & 1 <> 0 THEN '1,'
                     ELSE ''
                END + CASE WHEN DaysOfMonth & 2 <> 0 THEN '2,'
                           ELSE ''
                      END + CASE WHEN DaysOfMonth & 4 <> 0 THEN '3,'
                                 ELSE ''
                            END + CASE WHEN DaysOfMonth & 8 <> 0 THEN '4,'
                                       ELSE ''
                                  END
                + CASE WHEN DaysOfMonth & 16 <> 0 THEN '5,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 32 <> 0 THEN '6,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 64 <> 0 THEN '7,'
                                   ELSE ''
                              END + CASE WHEN DaysOfMonth & 128 <> 0 THEN '8,'
                                         ELSE ''
                                    END
                + CASE WHEN DaysOfMonth & 256 <> 0 THEN '9,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 512 <> 0 THEN '10,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25,'
                             ELSE ''
                        END + CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26,'
                                   ELSE ''
                              END
                + CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28,'
                             ELSE ''
                        END
                + CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29,'
                       ELSE ''
                  END + CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30,'
                             ELSE ''
                        END AS DaysOfMonthString ,
                CASE WHEN Month = 4095 THEN 'every month, '
                     ELSE CASE WHEN Month & 1 <> 0 THEN 'Jan, '
                               ELSE ''
                          END + CASE WHEN Month & 2 <> 0 THEN 'Feb, '
                                     ELSE ''
                                END + CASE WHEN Month & 4 <> 0 THEN 'Mar, '
                                           ELSE ''
                                      END
                          + CASE WHEN Month & 8 <> 0 THEN 'Apr, '
                                 ELSE ''
                            END + CASE WHEN Month & 16 <> 0 THEN 'May, '
                                       ELSE ''
                                  END + CASE WHEN Month & 32 <> 0 THEN 'Jun, '
                                             ELSE ''
                                        END
                          + CASE WHEN Month & 64 <> 0 THEN 'Jul, '
                                 ELSE ''
                            END + CASE WHEN Month & 128 <> 0 THEN 'Aug, '
                                       ELSE ''
                                  END
                          + CASE WHEN Month & 256 <> 0 THEN 'Sep, '
                                 ELSE ''
                            END + CASE WHEN Month & 512 <> 0 THEN 'Oct, '
                                       ELSE ''
                                  END
                          + CASE WHEN Month & 1024 <> 0 THEN 'Nov, '
                                 ELSE ''
                            END + CASE WHEN Month & 2048 <> 0 THEN 'Dec, '
                                       ELSE ''
                                  END
                END AS MonthString ,
                CASE MonthlyWeek
                  WHEN 1 THEN 'first'
                  WHEN 2 THEN 'second'
                  WHEN 3 THEN 'third'
                  WHEN 4 THEN 'fourth'
                  WHEN 5 THEN 'last'
                END AS MonthlyWeekString ,
                ' starting ' + CONVERT (VARCHAR, StartDate, 101)
                + CASE WHEN EndDate IS NOT NULL
                       THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101)
                       ELSE ''
                  END AS StartEndString ,
                CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) + ':'
                + CASE WHEN DATEPART(minute, StartDate) < 10
                       THEN '0' + CONVERT(VARCHAR(2), DATEPART(minute,
                                                              StartDate))
                       ELSE CONVERT(VARCHAR(2), DATEPART(minute, StartDate))
                  END + CASE WHEN DATEPART(hour, StartDate) >= 12 THEN ' PM'
                             ELSE ' AM'
                        END AS StartTime
               FROM
                Schedule
             ),
        SuperEnhancedSchedule
          AS (
               SELECT
                EnhancedSched.ScheduleID ,
                EnhancedSched.Name ,
                EnhancedSched.StartDate ,
                EnhancedSched.Flags ,
                EnhancedSched.NextRunTime ,
                EnhancedSched.LastRunTime ,
                EnhancedSched.EndDate ,
                EnhancedSched.RecurrenceType ,
                EnhancedSched.MinutesInterval ,
                EnhancedSched.DaysInterval ,
                EnhancedSched.WeeksInterval ,
                EnhancedSched.DaysOfWeek ,
                EnhancedSched.DaysOfMonth ,
                EnhancedSched.Month ,
                EnhancedSched.MonthlyWeek ,
                EnhancedSched.State ,
                EnhancedSched.LastRunStatus ,
                EnhancedSched.ScheduledRunTimeout ,
                EnhancedSched.CreatedById ,
                EnhancedSched.EventType ,
                EnhancedSched.EventData ,
                EnhancedSched.Type ,
                EnhancedSched.ConsistancyCheck ,
                EnhancedSched.Path , -- spec what you need.
                CASE WHEN RecurrenceType = 1
                     THEN 'At ' + StartTime + ' on '
                          + CONVERT(VARCHAR, StartDate, 101)
                     WHEN RecurrenceType = 2
                     THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 ))
                          + ' hour(s) and '
                          + CONVERT(VARCHAR, ( MinutesInterval % 60 ))
                          + ' minute(s), ' + 'starting '
                          + CONVERT (VARCHAR, StartDate, 101) + ' at '
                          + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6)
                          + ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109),
                                            25, 2)
                          + CASE WHEN EndDate IS NOT NULL
                                 THEN ' and ending '
                                      + CONVERT (VARCHAR, EndDate, 101)
                                 ELSE ''
                            END
                     WHEN RecurrenceType = 3
                     THEN 'At ' + StartTime + ' every '
                          + CASE DaysInterval
                              WHEN 1 THEN 'day, '
                              ELSE CONVERT(VARCHAR, DaysInterval) + ' days, '
                            END + StartEndString
                     WHEN RecurrenceType = 4
                     THEN 'At ' + StartTime + ' every '
                          + CASE WHEN LEN(DaysOfWeekString) > 1
                                 THEN LEFT(DaysOfWeekString,
                                           LEN(DaysOfWeekString) - 1)
                                 ELSE ''
                            END + ' of every '
                          + CASE WHEN WeeksInterval = 1 THEN ' week,'
                                 ELSE CONVERT(VARCHAR, WeeksInterval)
                                      + ' weeks,'
                            END + StartEndString
                     WHEN RecurrenceType = 5
                     THEN 'At ' + StartTime + ' on day(s) '
                          + CASE WHEN LEN(DaysOfMonthString) > 1
                                 THEN LEFT(DaysOfMonthString,
                                           LEN(DaysOfMonthString) - 1)
                                 ELSE ''
                            END + ' of ' + MonthString + StartEndString
                     WHEN RecurrenceType = 6
                     THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString
                          + ' '
                          + CASE WHEN LEN(DaysOfWeekString) > 1
                                 THEN LEFT(DaysOfWeekString,
                                           LEN(DaysOfWeekString) - 1)
                                 ELSE ''
                            END + ' of ' + MonthString + StartEndString
                     ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0,
                                            6) + ' '
                          + SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2)
                          + StartEndString
                END ScheduleTextDefinition
                FROM EnhancedSched
             )
    SELECT
        *
        -- This has the same columns as the native [dbo].Schedule table plus a field called "SheduleTextDefinition"
        -- You can use "SuperEnhancedSchedule" in place of the usual SSRS.Schedule table, joining to subscriptions and such.
    FROM
        SuperEnhancedSchedule
    Thanks



    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem.


    Thursday, October 2, 2014 6:51 PM
  • Hi Pungigi,

    Based on your description, you  want to get all the Meta Data of the report Schedule, right?
    The related schedule information are in the tables of the ReportServer DB, please find the tables below:

    Subscriptions; 
    ReportSchedule;
    dbo.Schedule;
    dbo.ExecutionLog  ;    
    dbo.Catalog;
    sysjobsteps;
    dbo.sysjobschedules;
    dbo.Notifications;
    dbo.Event;

    I have tested on my local environment and you can take reference of the query provided by PrajapatiNeha  to get the Meta data of all the Schedules.

    If you want to get the schedules of all the subscriptions, you can execute query below:

    USE [ReportServer];   ---- You may change the database name. 
    GO 
    SELECT USR.UserName AS SubscriptionOwner 
          ,SCH.ScheduleID
          ,SCH.Name
          ,SCH.Flags 
          ,SCH.StartDate 
          ,SCH.LastRunTime 
          ,SCH.NextRunTime 
          ,SCH.EndDate 
          ,SCH.RecurrenceType 
          ,SCH.MinutesInterval 
          ,SCH.DaysInterval 
          ,SCH.WeeksInterval 
          ,SCH.DaysOfWeek 
          ,SCH.DaysOfMonth 
          ,SCH.Month 
          ,SCH.MonthlyWeek 
          ,SCH.State 
          ,SCH.LastRunStatus 
          ,SCH.ScheduledRunTimeout
          ,SCH.CreatedById 
          ,SCH.EventData 
          ,SCH.Type 
          ,SCH.ConsistancyCheck
          ,SCH.Path 
          ,SUB.SubscriptionID   
          ,SUB.ModifiedDate 
          ,SUB.[Description] 
          ,SUB.EventType 
          ,SUB.DeliveryExtension 
          ,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];

    You can find the details query about the question you are facing:

    1.  Get the Total numbers of Schedules, first start time and Last completed time on a given day

    select  count(ScheduleID) as TotalSchedule,
            min(StartDate) as FirstStart,
            Max(StartDate) as LastCompleted
    from dbo.Schedule
    where CONVERT (VARCHAR, StartDate, 101) like 'MM/DD/YYY';----09/01/2014

    2.  How many schedules of subscription created for each type(Email, FileShare)

    select COUNT(DeliveryExtension) as TotalDelivery from 
    (SELECT 
    SUB.DeliveryExtension 
    FROM dbo.Subscriptions AS SUB 
    INNER JOIN dbo.Users AS USR 
    ON SUB.OwnerID = USR.UserID 
    INNER JOIN dbo.[Catalog] AS CAT 
    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 ) a
    where DeliveryExtension like '%FileShare'
    --where DeliveryExtension like '%Email'

    Below links are for your reference about schedule and subscription:
    http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx
    http://stackoverflow.com/questions/8334642/ssrs-2008-r2-get-human-readable-schedule-information-from-reportserver-db

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu


    Tuesday, October 7, 2014 5:30 AM
    Moderator
  • Problem is ... most of my tables don't have data...

    Here are the ones that do...

    ChunkSegmentMapping 3,232
    ConfigurationInfo 23
    DataSets 1,234
    DataSource 401
    DB UpgradeHistory 31
    ExecutionLogStorage 136,849
    Keys 2
    Policies 4
    PolicyUserRole 69
    Roles 8
    SecData 4
    Segment 3,232
    SegmentedChunk 879
    ServerUpgradeHistory 2
    SnapshotData 373
    UpgradeInfo 1
    Users 9

    SOOOO Confused


    Tuesday, October 7, 2014 4:50 PM
  • Hi Pungigi,

    Base on your description that most of the tables don’t have any records, right?

    You can select  below tables one by one to check if all of the schedule related tables have no records:

    dbo.Subscriptions; 
    dbo.ReportSchedule;
    dbo.Schedule;
    dbo.ExecutionLog  ;    
    dbo.Catalog;
    dbo.sysjobs;
    dbo.sysjobsteps;
    dbo.sysjobschedules;
    dbo.Notifications;
    dbo.Event;

    When you create a subscription several things are added to the RS server:

    1. A row is placed in the dbo.Subscriptions table
    2. A row is placed in the dbo.Schedule and dbo.ReportSchedule tables
    3. A SQL Server Agent job is created(a row is placed in the dbo.sysjobs and dbo.sysjobsteps tables)

    So please check below first:

    • Did you scheduled any subscription on the given day?
      If you did ,you will have records in the tables of step1,2,3, If didn’t ,you will not have any records in the tables of step1 and step3.
    • Did you created any shared schedules ,Scheduled Cache or Scheduled  snapshot on the given day?
      If you did ,you will have records in the tables of step2

    If you have check above but still no records, The link about subscription troubleshooting are for your reference.
    http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx
    http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu

    Wednesday, October 8, 2014 6:34 AM
    Moderator
  • This sample is over coded (AKA spaghetti), Below might work better and comes from multiple combine sources.

                      SELECT
    C1.Name AS [Report Name]
    ,C.Name AS [Folder Name]
    ,
    CASE  
    WHEN S.RecurrenceType=1 
    THEN 'Once' 
    WHEN S.RecurrenceType=2 
    THEN 'Hourly' 
    WHEN S.RecurrenceType=3 
    THEN 'Daily' 
    WHEN S.RecurrenceType=4 
    THEN 'Weekly' 
    WHEN S.RecurrenceType IN (
       5
       ,6
      ) 
    THEN 'Monthly' 
    END 
    AS [Recurrence Type] 
                    ,ISNULL(
         S.WeeksInterval,0
        ) AS [Weekly Interval]
    ,
    CASE DaysOfWeek
    WHEN  1 
    THEN 'Monday'
    WHEN 2 
    THEN 'Tuesday'
    WHEN  4 
    THEN 'Wednesday'
    WHEN  8 
    THEN 'Thursday'
    WHEN  16 
    THEN 'Friday'
    WHEN  32 
    THEN 'Saturday'
    WHEN  64 
    THEN 'Sunday'
    WHEN  62 
    THEN 'Monday – Friday'
    WHEN  120 
    THEN 'Wednesday – Saturday'
    WHEN  126 
    THEN 'Monday – Saturday'
    WHEN  127 
    THEN 'Daily'
    ELSE 'Monthly'
    END 
    AS DaysOfWeek                
    ,ISNULL(
         S.DaysOfMonth, 0
        ) AS DaysOfMonth
    ,CAST(
       S.StartDate AS TIME(0)
    ) AS StartTime
       FROM
                    Schedule S
    INNER JOIN ReportSchedule RS ON S.ScheduleID = RS.ScheduleID
    INNER JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID
    INNER JOIN [Catalog] C1 ON SUB.Report_OID = C1.ItemID
    INNER JOIN [Catalog] C ON C1.ParentID = C.ItemID
    Monday, October 30, 2017 4:58 PM