Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

SSRS – Identify a scheduled SSRS Report

Initiating subscription is a tedious task in SQL Server Reporting Services. Each subscription in Reporting Services is setup as a SQL Server Agent job, but the job names are Unique Identifier its hard to remember and re-call. Have complete information in single query makes one easy to validate and execute the report with ease

You can manually invoke the job using T-SQL

USE [msdb] EXEC sp_start_job @job_name = ’4008B6C3-D588-4D31-A565-F9527015DE79′

In the below code change the <ReportServer> parameter to corresponding ReportServer database name.

USE <ReportServer> 
GO 
SELECT 
sj.[name] AS [Job Name], 
c.[Name] AS [Report Name], 
su.SubscriptionID, 
su.OwnerID, 
c.[Path], 
su.Description, 
su.EventType, 
su.LastStatus, 
su.LastRunTime, 
sjs.next_run_date, 
CASE ss.[enabled] 
        WHEN 1 THEN 'Yes' 
        WHEN 0 THEN 'No' 
      END AS [IsEnabled] 
    , CASE  
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts' 
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle' 
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring' 
        WHEN [freq_type] = 1 THEN 'One Time' 
      END [ScheduleType] 
    , CASE [freq_type] 
        WHEN 1 THEN 'One Time' 
        WHEN 4 THEN 'Daily' 
        WHEN 8 THEN 'Weekly' 
        WHEN 16 THEN 'Monthly' 
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval' 
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts' 
        WHEN 128 THEN 'Start whenever the CPUs become idle' 
      END [Occurrence] 
    , CASE [freq_type] 
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)' 
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                    + ' week(s) on ' 
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END 
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END 
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))  
                     + ' of every ' 
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' 
        WHEN 32 THEN 'Occurs on ' 
                     + CASE [freq_relative_interval] 
                        WHEN 1 THEN 'First' 
                        WHEN 2 THEN 'Second' 
                        WHEN 4 THEN 'Third' 
                        WHEN 8 THEN 'Fourth' 
                        WHEN 16 THEN 'Last' 
                       END 
                     + ' '  
                     + CASE [freq_interval] 
                        WHEN 1 THEN 'Sunday' 
                        WHEN 2 THEN 'Monday' 
                        WHEN 3 THEN 'Tuesday' 
                        WHEN 4 THEN 'Wednesday' 
                        WHEN 5 THEN 'Thursday' 
                        WHEN 6 THEN 'Friday' 
                        WHEN 7 THEN 'Saturday' 
                        WHEN 8 THEN 'Day' 
                        WHEN 9 THEN 'Weekday' 
                        WHEN 10 THEN 'Weekend day' 
                       END 
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                     + ' month(s)' 
      END AS [Recurrence] 
    , CASE [freq_subday_type] 
        WHEN 1 THEN 'Occurs once at '  
                    + STUFF( 
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
        WHEN 2 THEN 'Occurs every '  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '  
                    + STUFF( 
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
                    + ' & '  
                    + STUFF( 
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
        WHEN 4 THEN 'Occurs every '  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '  
                    + STUFF( 
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
                    + ' & '  
                    + STUFF( 
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
        WHEN 8 THEN 'Occurs every '  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '  
                    + STUFF( 
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
                    + ' & '  
                    + STUFF( 
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, ':') 
                            , 6, 0, ':') 
      END [Frequency] 
    , STUFF( 
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-') 
                , 8, 0, '-') AS [ScheduleUsageStartDate] 
    , STUFF( 
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-') 
                , 8, 0, '-') AS [ScheduleUsageEndDate] 
    , [ss].[date_created] AS [ScheduleCreatedOn] 
    , ss.[date_modified] AS [ScheduleLastModifiedOn] 
FROM msdb.dbo.sysjobs AS sj  
INNER JOIN dbo.ReportSchedule AS rs 
ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))  
INNER JOIN msdb.dbo.sysjobschedules sjs on sj.job_id=sjs.job_id 
INNER JOIN msdb.dbo.sysschedules ss on ss.schedule_id=sjs.schedule_id 
INNER JOIN dbo.Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID  
INNER JOIN dbo.[Catalog] c ON su.Report_OID = c.ItemID 
order by [Report Name]

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...