Tuesday 17 April 2012

Failed Subscriptions

From time to time reports fail and you need know about them so that they can be sent to the relevant recipients without any delay. The script below has been used to create a report with data driven subscription.

SELECT
'Failed Subscriptions' as Type,
convert(char(40),c.path) as Folder_Path,
c.name as Report_Name,
case when len(convert(char(8000),parameters)) =19 then 'No Parameters' else
convert(char(700),parameters) end as parameters,
s.description as Recipients,s.lastruntime,
convert(char(500),laststatus) as Error,
'PathName' + replace(replace(c.path,'/','%2f'),' ','+') + '&ViewMode=Detail'
as [ReportURL],
'Pathname' + replace(replace(c.path,'/','%2f'),' ','+')
+ '&SelectedTabId=PropertiesTab&ViewMode=Detail&SelectedSubTabId=SubscriptionsTab'
as [SubsURL],
right(UserName,len(username)-12) as created_by,
case when s1.eventtype <>'SharedSchedule' then 'ReportSpecific' else s1.Name end as Schedule_Name
from subscriptions s
join catalog c on s.report_oid=c.itemid
join users u on s.ownerid = u.userid
LEFT OUTER join reportschedule rs on rs.subscriptionid = s.subscriptionid
LEFT OUTER join schedule s1 on s1.scheduleid = rs.scheduleid
where
(laststatus like '%fail%'
or LastStatus like 'error:%')

No comments:

Post a Comment

Keep SSRS (SSRS2016) report manager awake

When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine.  There are a ...