Report failed SSRS subscriptions

,

I use this script to generate a dataset for a report that tells me when subscriptions fail to send e-mail or post reports to file shares based on the value of the lastStatus column in the subscriptions table.

Trouble is it generates a report and e-mail whether there are failures or not. If you have the Enterprise edition, you can do a data-driven sub that will get around this.

Failed subs will stay on the list until they run successfully.

select
cat.Name,
u.username 'Report Author',
replace(S.[DeliveryExtension],'report server ','') 'Type',
S.[ExtensionSettings],
Modified.[UserName] 'Subscription Modifier/Creator',
S.[ModifiedDate],
replace(S.[Description],'send e-mail to ','') 'Distribution List',
S.[LastStatus],
CAT.[Path],
S.[LastRunTime]
from
[Subscriptions] S 

inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
inner join [Users] Owner on S.OwnerID = Owner.UserID
inner join [Users] Modified on S.ModifiedByID = Modified.UserID
inner join users u on cat.createdbyid = u.userid
left outer join [SecData] SD on CAT.PolicyID = SD.PolicyID AND SD.AuthType = 1
left outer join [ActiveSubscriptions] A with (NOLOCK) on S.[SubscriptionID] = A.[SubscriptionID]
where S.[LastStatus] not LIKE '%was written%' and S.[LastStatus] not LIKE '%mail sent%' and S.[LastStatus] not LIKE '%New Subscription%' and S.[LastStatus] not LIKE '%been saved%'
Order by s.lastruntime desc

Rate

4.75 (4)

Share

Share

Rate

4.75 (4)