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 witout any delay. The script below has been used to create a report with data driven subscription.

'Failed Subscriptions' as Type,
convert(char(40),c.path) as Folder_Path, 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
(laststatus like '%fail%'
or LastStatus like 'error:%')