When you create a job schedule in SSRS 2008, the server creates a SQL Server Agent job with a GUID name. Is there a script that can be run to rename the shared job schedule in the ReportServer database to another name? I would like to do this to make it easier to organize subscriptions in the Job Activity Monitor since I have a very large number of subscriptions mixed in with other jobs on my database server.
I wrote the following code to modify the GUIDs:
ALTER TABLE ReportServer.dbo.ReportSchedule
NOCHECK CONSTRAINT
FK_ReportSchedule_Schedule
UPDATE ReportServer.dbo.ReportSchedule
SET ScheduleID = '0' + SUBSTRING(CONVERT(VARCHAR(36), scheduleid), 2, 35)
WHERE ScheduleID NOT LIKE '[0-9a-b]%'
UPDATE ReportServer.dbo.Schedule
SET ScheduleID = '0' + SUBSTRING(CONVERT(VARCHAR(36), scheduleid), 2, 35)
WHERE ScheduleID NOT LIKE '[0-9a-b]%'
ALTER TABLE ReportServer.dbo.ReportSchedule
CHECK CONSTRAINT
FK_ReportSchedule_Schedule
This code works for SQL Server 2008. After making these changes, I manually modified the jobs to reference the new names and this solved my problem. It didn't go as far as I wanted, but it at least sorted all of them jobs to the top of the activity monitor.
There is a post about this on main SSC site. Here is the link: http://www.sqlservercentral.com/Forums/Topic254010-150-1.aspx
and here is the paste of that post from Steve (http://www.sqlservercentral.com/Forums/UserInfo76010.aspx):
It looks like the sqlagent job name is the guid actually used for the schedule stored within the database. Since the app (i.e. RS itself) will be relying on this, you can't chnge this.
But, you could use a view like below to produce a quick reference of what each guid (sql agent job) is doing.
select
Schedule.ScheduleID as SQLAgent_Job_Name,
Subscriptions.Description as sub_desc,
Subscriptions.DeliveryExtension as sub_delExt,
[Catalog].Name as reportname, [catalog].path as reportpath
from reportschedule inner join Schedule
on ReportSchedule.ScheduleID = Schedule.ScheduleID
inner join Subscriptions
on ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
inner join [Catalog]
on ReportSchedule.ReportID = [Catalog].ItemID
and Subscriptions.Report_OID = [Catalog].ItemID;
