question

Registered User avatar image
Registered User asked

Rename SSRS Subscription GUID’s

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.

sql-server-2008ssrs
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Registered User avatar image
Registered User answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered

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;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.