December 12, 2017 at 6:02 am
Requirement: Subscription failed for more than 10 days, then disable and Delete the Subscription
I found script something like this..
select c.Name as ReportName,
s.EventType,
s.Description as SubscriptionDescription,
s.LastStatus as LastSubscriptionStatus,
s.LastRunTime SubscriptionLastRunTime,
case
when recurrencetype = 1 then 'One Time'
when recurrencetype = 2 then 'Hourly'
when recurrencetype = 4 then 'Daily'
when recurrencetype = 5 then 'Monthly'
when recurrencetype = 6 then 'Month Week'
else 'Other'
end as RecurranceType,
s.DeliveryExtension,
u.UserName as SubscriptionSetUpBy,
s.ModifiedDate as SubscriptionLastModifiedDate
from [ReportServer].[dbo].[Subscriptions] s
join [ReportServer].[dbo].[Catalog] c
on c.ItemID = s.Report_OID
join [ReportServer].[dbo].[Users] u
on u.UserID = s.OwnerID
join [ReportServer].[dbo].[reportschedule] rs
on c.itemid = rs.reportid
and s.subscriptionid = rs.subscriptionid
join [ReportServer].[dbo].[schedule] sch
on rs.scheduleid = sch.scheduleid
where s.EventType <> 'RefreshCache'
and s.LastRunTime < dateadd(D, -10, getdate())
and s.LastStatus='%Failure%'
order by c.name
To Disable and Delete the Subscription what are the steps to be added..
Thanks in advance
December 12, 2017 at 7:19 am
Once you identify the subscription, go to Report Manager, go to subscriptions, find the subscription and delete it.
Create and Manage Subscriptions for Native Mode Report Servers
If you wanted to write code to use the web services to do this, look at the DeleteSubscription method
If you wanted to use Powershell to delete them instead, import the ReportingServicesTools module and use the Remove-RSSubscription cmdlet.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply