create procedure BD_FailedSubscriptions_prc

as

select
cat.Name,
u.username ‘Report Author’,
replace(S.[DeliveryExtension],’report server ‘,”) ‘Type’,
S.[ExtensionSettings] as [Parameters],
Modified.[UserName] as LastModifiedBy,
S.[ModifiedDate],
replace(S.[Description],’send e-mail to ‘,”) as DistributionList,
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