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
A guide for getting around the missing data driven subscription feature in Standard editions of Microsoft SQL Server.
2016-08-29
7,392 reads
This article presents an excerpt from the book, Microsoft SQL Server 2005 Reporting Services for Dummies, by Mark Robinson. Learn how to produce interesting navigation and drill down reporting using the basic tools provided within SQL Server 2005 Reporting Services.
2008-02-22
2,216 reads
Learn how to build Custom Reports, without installing Reporting Services, using a new feature found in Microsoft Service Pack 2 (SP2) for SQL Server 2005.
2008-02-07
4,068 reads
Report parameters assist in narrowing down a report for better analysis.
2008-01-17
2,614 reads
You can build some very complex reports in Reporting Services and longtime author Raj Vasant brings us a short tutorial on how you can implement internal navigation structures in your reports.
2008-01-10
5,215 reads
