CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
SQL Learner
SQL Learner

Reputation: 53

How to delete old subscriptions

I have subscriptions which are 3 years old. I just want to delete 2 years old unused, Old and Orphan subscriptions.

Upvotes: 4

Views: 5305

Answers (4)

GLeb
GLeb

Reputation: 111

You can automate the process with dynamic sql query from ReportServer tables: Catalog, ReportSchedule, Subscriptions. These statements I caught in Profiler when I deleted the subscription from web interface.

declare @DelSubscriptionID varchar(36);

select @DelSubscriptionID = cast(SubscriptionID as varchar(40))
from ReportSchedule
where ScheduleID = 'YourJobName';

exec AddSubscriptionToBeingDeleted @SubscriptionID = @DelSubscriptionID;  
exec DeleteSubscription @SubscriptionID = @DelSubscriptionID;

Upvotes: 1

Trubs
Trubs

Reputation: 3042

The script below shows all subscriptions/schedules/when they were create/when they were last executed (apologies for lack of attribution)

you could use it to find where the subscriptions are you want to dump and manually go and remove them using @Kavins directions

--SSRS SUbscriptions

USE ReportServer
SELECT AgentJobName = sysjobs.NAME
        ,DataDriven = Case when subs.DataSettings is null then 0 else 1 end
         ,Recurrence = CASE WHEN recurrencetype = 1 THEN 'One off'
             WHEN recurrencetype = 2 THEN  'Every '+ CAST(MinutesInterval AS VARCHAR(4))+' Minutes'
             WHEN recurrencetype = 3 THEN 'Every '+ CAST(DaysInterval AS VARCHAR(4))+' Days'
             WHEN recurrencetype = 4 THEN 'Every '+ CAST(WeeksInterval AS VARCHAR(4))+' Weeks'
             WHEN recurrencetype = 5 THEN 'Monthly'
             WHEN recurrencetype = 6 THEN 'Month Week' END
         ,Schedule = ISNULL(CASE
             WHEN daysofmonth = 1 THEN '1st Day of'
             WHEN daysofmonth = 2 THEN '2nd Day of'
             WHEN daysofmonth = 4 THEN '3rd Day of'
             WHEN daysofmonth = 8 THEN '4th Day of'
             WHEN daysofmonth = 16 THEN '5th Day of'
             WHEN daysofmonth = 32 THEN '6th Day of'
             WHEN daysofmonth = 64 THEN '7th Day of'
             WHEN daysofmonth = 128 THEN '8th Day of'
             WHEN daysofmonth = 256 THEN '9th Day of'
             WHEN daysofmonth = 512 THEN '10th Day of'
             WHEN daysofmonth = 1024 THEN '11th Day of'
             WHEN daysofmonth = 2048 THEN '12th Day of'
             WHEN daysofmonth = 4096 THEN '13th Day of'
             WHEN daysofmonth = 8192 THEN '14th Day of'
             WHEN daysofmonth = 16384 THEN '15th Day of'
             WHEN daysofmonth = 32768 THEN '16th Day of'
             WHEN daysofmonth = 65536 THEN '17th Day of'
             WHEN daysofmonth = 131072 THEN '18th Day of'
             WHEN daysofmonth = 262144 THEN '19th Day of'
             WHEN daysofmonth = 524288 THEN '20th Day of'
             WHEN daysofmonth = 1048576 THEN '21st Day of'
             WHEN daysofmonth = 2097152 THEN '22nd Day of'
             WHEN daysofmonth = 4194304 THEN '23th Day of'
             WHEN daysofmonth = 8388608 THEN '24th Day of'
             WHEN daysofmonth = 16777216 THEN '25th Day of'
             WHEN daysofmonth = 33554432 THEN '26th Day of'
             WHEN daysofmonth = 67108864 THEN '27th Day of'
             WHEN daysofmonth = 134217728 THEN '28th Day of'
             WHEN daysofmonth = 268435456 THEN '29th Day of'
             WHEN daysofmonth = 536870912 THEN '30th Day of'
             WHEN daysofmonth = 1073741824 THEN '31st Day of'
             WHEN daysofmonth = 8193 THEN '1st and 14th day Day of' END
             ,'')
            +
            ISNULL(
            CASE WHEN Month = 4095 THEN 'Every Month'
             WHEN MONTH = 585 THEN 'Jan,April,July,October'
             WHEN Month = 1 THEN 'Jan'
             WHEN Month = 2 THEN 'Feb'
             WHEN Month = 4 THEN 'Mar'
             WHEN Month = 8 THEN 'Apr'
             WHEN Month = 16 THEN 'May'
             WHEN Month = 32 THEN 'Jun'
             WHEN Month = 64 THEN 'Jul'
             WHEN Month = 128 THEN 'Aug'
             WHEN Month = 256 THEN 'Sep'
             WHEN Month = 512 THEN 'Oct'
             WHEN Month = 1024 THEN 'Nov'
             WHEN Month = 2048 THEN 'Dec' END
             ,'')
            +
            ISNULL(
            CASE WHEN DaysOfWeek = 1 THEN 'Sunday'
            WHEN DaysOfWeek = 2 THEN 'Monday'
            WHEN DaysOfWeek = 4 THEN 'Tuesday'
            WHEN DaysOfWeek = 8 THEN 'Wednesday'
            WHEN DaysOfWeek = 16 THEN 'Thursday'
            WHEN DaysOfWeek = 32 THEN 'Friday'
            WHEN DaysOfWeek = 64 THEN 'Saturday'
            WHEN DaysOfWeek = 62 THEN 'Monday - Friday'
            WHEN DaysOfWeek = 120 THEN 'Wednesday - Saturday'
            WHEN DaysOfWeek = 126 THEN 'Monday - Saturday'
            WHEN DaysOfWeek = 127 THEN 'Daily'
            END
            ,'')
         + ' '
         + CAST(DATEPART(hh,s.StartDate)AS VARCHAR(2))
         + CASE
            WHEN LEN(CAST(DATEPART(n,s.StartDate)AS VARCHAR(2))) = 1
            THEN ':0' + CAST(DATEPART(n,s.StartDate)AS VARCHAR(2))
   ELSE ':' + CAST(DATEPART(n,s.StartDate)AS VARCHAR(2))
   END
        ,IncludeReport
        ,ReportFormat
        ,c.Name
        ,c.Path
        ,subs.DESCRIPTION
        ,subs.laststatus
        ,subs.lastruntime
FROM msdb.dbo.sysjobs
INNER JOIN  dbo.ReportSchedule rs ON sysjobs.name = CAST(rs.ScheduleID AS VARCHAR(255)) 
INNER JOIN  dbo.Schedule s  ON rs.ScheduleID = s.ScheduleID
INNER JOIN  dbo.Catalog c ON rs.ReportID = c.ItemID
INNER JOIN Subscriptions subs ON subs.SubscriptionID = rs.subscriptionid 
INNER JOIN (SELECT      SubscriptionId, 
            IncludeReport=CASE WHEN SUBSTRING(ExtensionSettings,CHARINDEX('IncludeReport',CAST(ExtensionSettings as varchar(MAX)),0)+27,4) = 'True' THEN 'True' ELSE 'False' end,
            ReportFormat=   CASE WHEN SUBSTRING(ExtensionSettings,CHARINDEX('IncludeReport',CAST(ExtensionSettings as varchar(MAX)),0)+27,4) = 'True' 
                            THEN SUBSTRING(ExtensionSettings,  --field we are searching for
                                            CHARINDEX('RenderFormat',CAST(ExtensionSettings as varchar(MAX)),0)+26,  -- starting position
                                                CHARINDEX('</V',ExtensionSettings,CHARINDEX('RenderFormat',CAST(ExtensionSettings as varchar(MAX)),0)+26) -- length
                                                    -(CHARINDEX('RenderFormat',CAST(ExtensionSettings as varchar(MAX)),0)+26))  END

            FROM Subscriptions s
            )Attachments ON Attachments.SubscriptionId = subs.SubscriptionId
ORDER BY c.Path,CAST(subs.DataSettings AS VARCHAR(3)),recurrencetype

Upvotes: 3

Arthur D
Arthur D

Reputation: 86

Kavin's answer seems to cover a general usecase, however I'd also like to add that the subscriptions are objects on the database and can be deleted if you need to. Navigate to your report server database and you can query all subscriptions.

Upvotes: 0

Hell Boy
Hell Boy

Reputation: 981

Go to ==>Report Manager (SSRS Native Mode) .

In Report Manager, click My Subscriptions on the global toolbar and navigate to the subscription you want to modify or delete.

Alternatively, on the Subscriptions tab of an open report, find the subscription that you want to modify or delete. Perform one of the following:

To delete a subscription, select the check box next to the subscription, and then click Delete.

Note: A subscription is deleted automatically if the underlying report is deleted.

Upvotes: 1

Related Questions