November 19, 2008 at 8:41 am
I have setup several reports for the accounting department and I gave them subscription rights. One lady was responsible for making sure the sales people got a list of aging invoices each week, which she did through the automated subscriptions. About two months ago the accounting manager left and about a month ago the woman who monitored the aging reports was fired. The new manager didn't know anything about how the reports were setup. I guess she thought that IT monitored them all. Anyways, after a month of neglect, several people were not getting their reports and some pretty big invoices went unpaid without anyone's attention. So the new manager was wanting me to set up some kind of alerting system to notify whoever that the reports were not running and such.
To my knowledge, I do not know of any system in Report Services that does that, such as when a job fails in the SQL Server Agent. Am I wrong? I know the subscriptions are setup as jobs in the Agent, but they are without logical names (ex 03153828-D2F8-435F-AF6B-282BCE5EA515), so I can never tell which report goes to which job. Also, this should not be a responsibility of the IT department to monitor the subscriptions of AR. The fix would have to be a setting change so that someone in AR would be notified of failures.
Thanks
Adam
November 19, 2008 at 12:00 pm
I use the query below 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. You could certainly tweak it to pull just AR reports and set up a subscription that sends this report to them. 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.
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
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
November 19, 2008 at 12:59 pm
Toolman,
Have you posted this script in the script library? I think this would be helpful to many people.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2008 at 1:22 pm
Didn't even know we could do such things Jack.
At your suggestion, it's been done. Thanks
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
November 19, 2008 at 2:23 pm
Thanks Toolman, that works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply