/******************************
** FileName: SetJobDescriptionSSRSJobs.sql 
** Description: Creates and stores a description for jobs created by SSRS.
** Author: Arthur Baan
** Date: 05-01-2015
**************************
** Change History
**************************
** Date			Author		Description	
** --------		-----------	------------------------------------
** 05-01-2015   Arthur Baan	Initial version
*******************************/
/* Use <ctrl><shft><M> to enter the name of your ReportServer database. */
use <ReportServerDatabase, sysname, ReportServer>
go

/* Variables */
declare 
  @ScheduleID uniqueidentifier
, @ReportDescription nvarchar(4000)
, @ScheduleDescription nvarchar(4000)
, @JobDescription nvarchar(4000)
, @Time nvarchar(10)

/* Temporary table to store the job schedule information. */
create table #JobSchedule
( schedule_id int null
, schedule_name nvarchar(128) null
, enabled int null
, freq_type int null
, freq_interval int null
, freq_subday_type int null
, freq_subday_interval int null
, freq_relative_interval int null
, freq_recurrence_factor int null
, active_start_date int null
, active_end_date int null
, active_start_time int null
, active_end_time int null
, date_created datetime null
, schedule_description nvarchar(4000) null
, next_run_date int null
, next_run_time int null
, schedule_uid uniqueidentifier null
, job_count int null)

/* Get all schedules from the ReportServer database. */
select ScheduleID
into #Schedules
from dbo.[Schedule] s

/* Start looping through the schedules. */
while (select count(1) from #Schedules) > 0
begin
	select top 1
	@ScheduleID = ScheduleID
	from #Schedules

	/* Clear temp table and variables. */
	delete from #JobSchedule
	set @ReportDescription = ''
	set @JobDescription = ''
	set @ScheduleDescription = ''

	/* Check to see if the schedule exists as a job in MSDB. */
	if exists (select 1 from msdb.dbo.sysjobs where name = cast(@ScheduleID as nvarchar(128)))
	begin
		/* Get the schedule information of the job. */
		/* TODO: Create code to handle multiple schedules. */
		insert into #JobSchedule
		exec msdb.dbo.sp_help_jobschedule @job_name = @scheduleID,@include_description = 1

		/* Store the schedule description (this is the schedule from MSDB not the reportserver database). */
		select @ScheduleDescription = schedule_description
		from #JobSchedule
		
		/* Try to get the time part of the schedule description. */
		select @Time = try_convert(int,reverse(substring(reverse(@ScheduleDescription),1,charindex(' ',reverse(@ScheduleDescription),1))))

		/* If there is a time part, format it to a normal layout and create a better schedule description. */
		/* This code is "borrowed" from the MSDB function dbo.agent_datetime */
		if @Time is not null
		begin
			select @ScheduleDescription=replace(@ScheduleDescription,@Time,cast(
			convert(time,
			convert(nvarchar(2),@Time / 10000) + N':' +        
			convert(nvarchar(2),(@Time % 10000)/100) + N':' +        
			convert(nvarchar(2),@Time % 100),120) as nvarchar(8)))
		end
		/* Cleanup the schedule description. */
		select @ScheduleDescription= replace(@ScheduleDescription,' months(s)',' month(s)') /* this is just wrong. */
		select @ScheduleDescription= replace(@ScheduleDescription,' 1 week(s) ',' week ') /* this is for a better look of the description. */
		select @ScheduleDescription= replace(@ScheduleDescription,' 1 month(s) ',' month ') /* this is for a better look of the description. */

		/* Create the description with the reports using this schedule. */
		select 
		@ReportDescription += ca.Name + ' [' + 
		 case count(1) when 1 then min(su.[Description]) else 'Multiple subscriptions' end +']' +char(13) + char(10) /*If there is one subscription show this other return 'Multiple subscriptions'. */
		from dbo.[ReportSchedule] rs
		inner join dbo.[Catalog] ca on rs.ReportID = ca.ItemID
		inner join dbo.[Schedule] s on rs.ScheduleID = s.scheduleID
		inner join dbo.[Subscriptions] su on rs.SubscriptionID = su.SubscriptionID
		where s.ScheduleID = @scheduleID
		group by s.ScheduleID, ca.Name

		/* If the new job description is to big skip the schedule description. */
		if len(@ScheduleDescription) + len('Report(s):') + len(@ReportDescription) <=510
		begin
			set @JobDescription = @ScheduleDescription + char(13) + char(10) + 'Report(s):' +char(13) + char(10) + @ReportDescription
		end
		else
		begin
			set @JobDescription = @ReportDescription
		end
		
		/* Update the job with the new description. */
		exec msdb.dbo.sp_update_job @job_name = @ScheduleID, @description = @JobDescription
	
	end
	
	delete from #Schedules
	where ScheduleID = @ScheduleID
end

/* Drop the temporary tables*/
drop table #Schedules
drop table #JobSchedule

