January 30, 2008 at 3:28 am
I've got a data-drive subscription (but I guess this applies to all subscriptions). Everything works fine and as expected. But, in anticipation of possible failures I'd like to know if it's possible to setup email alerts that alert an administrator when something failed.
For example, if you look at the subscription details you'll see a Status column that will say something like this: "Done: 5 processed of 5 total; 5 errors."
In this particular example, if I look in the log file I'll see that the email couldn't be sent because one or more senders was malformed.
Unfortunately, unless I make it a point of looking in the error log (or the details of the subscription) I'll never know that there was an error. Furthermore, the error log isn't helpful in that it doesn't give details of which email addresses were malformed.
Ideally, I want someone to receive an alert whenever an error occurs on the subscription. I don't want to rely on someone having to manually check the subscription details to see if there was an error. In a perfect world I'd even get details of the error that could tell me which specific part of the subscription failed.
January 30, 2008 at 4:21 am
I have just scheduled a job agent job that checks the LastStatus field in the Subscriptions table in the reporting services database. This is the status displayed in report manager when you look at a subscription.
If you check the table regularly, you can send an email notification about reports that have failed.
January 30, 2008 at 4:53 am
Michael Earl (1/30/2008)
I have just scheduled a job agent job that checks the LastStatus field in the Subscriptions table in the reporting services database. This is the status displayed in report manager when you look at a subscription.If you check the table regularly, you can send an email notification about reports that have failed.
Thanks, that will work! Shame that it doesn't give specific error details but that can be seen in the error log, even though the log doesn't necessarily include detailed descriptions of the errors themselves.
But at least an email can be sent out on failure, which will then prompt further investigation.
January 30, 2008 at 8:16 am
I use the following query for a dataset gathering data from the Report Server database. It gets used in a report set to distribute by subscription each morning after our high subscription volume period. It'll get you the most error detail you can get without digging into the error log.
HTH
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
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
January 30, 2008 at 9:13 am
Thanks, much appreciated.
January 23, 2014 at 11:44 am
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%'
I use logic similar to the snippet posted above. My "status report" lists all subscription reports and their status. Since the Subscriptions table is being updated at the time my "status report" is running, I do NOT know the value of the LastStatus column for the status report itself. My report thus tells me my subscription has failed even though it completed successfully.
I need to test for the value of Subscriptions.LastStatus during the processing of the report. How can I do this?
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply