January 19, 2016 at 8:11 am
Hi All,
I have a SSRS subscription on a report which runs Monday - Friday (All week days) and It is working fine as expected if there is no failure, i.e, I am getting success email every time if runs the report but when there Is any failure I am getting any email failure email rather when I open subscription page of the report I see "Failure sending mail: An error has occurred during report processing.Mail will not be resent" , which is not even a clear message.
For that I need to check daily if there is any failure which is manual work to do so is there a way I can get failure email like success email and also the error message is not clear where can I able to get clear error message.
Thanks for any valuable thoughts in advance.
January 20, 2016 at 5:08 am
You can check status of subscription like this:
SELECT DISTINCT
E.NAME ,
E.PATH ,
D.DESCRIPTION ,
LASTSTATUS ,
EVENTTYPE ,
LASTRUNTIME ,
DATE_CREATED ,
DATE_MODIFIED
FROM REPORTSERVER.DBO.REPORTSCHEDULE A
JOIN MSDB.DBO.SYSJOBS B
ON CAST(A.SCHEDULEID AS VARCHAR(40)) = B.NAME
JOIN REPORTSERVER.DBO.REPORTSCHEDULE C
ON B.NAME = CAST(C.SCHEDULEID AS VARCHAR(40))
JOIN REPORTSERVER.DBO.SUBSCRIPTIONS D
ON CAST(C.SUBSCRIPTIONID AS VARCHAR(40)) = D.SUBSCRIPTIONID
JOIN REPORTSERVER.DBO.CATALOG E
ON D.REPORT_OID = E.ITEMID
WHERE D.LastStatus NOT LIKE '%Mail sent to%' AND D.LastStatus NOT LIKE '%The file%'
AND CONVERT(NVARCHAR, D.LastRunTime, 23) > CONVERT(NVARCHAR, getdate()-1, 23)
then create job.
in 1 step check iff count above query is >0 then go to step 2 there add command select 1/0 < Divide by zero error encountered. job returns an error.
Set notification on failiture.
Now you have mail when subscription is failed. 🙂
January 20, 2016 at 12:46 pm
Hi ,
I am unable to run the query , which might be the permissions issue.
but is there a way i can set audit process that can be built to be sure it runs each night?
Thanks,
Kalyan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply