SSRS Subscription Error: "Failure sending mail: Report processing has been canceled by the user. Mail will not be resent."

Question

On the report Subscriptions page, under "Status" for Last Run, it has the following error:

Failure sending mail: Report processing has been canceled by the user. Mail will not be resent.

It's a subscription, no person canceled this. Maybe there is a problem with a service.

I can run the report fine right now. The only log I can find is here:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

There are two lines for the report:

notification!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: Handling subscription 75b94483-618d-4463-b6c0-90916b92cb6 to report ReportName, owner: domain\user, delivery extension: Report Server Email. library!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: RenderForNewSession('/Reports/ReportName')

These lines don't indicate an error occured. I've been looking through all of the logs I could find and I can't find any sort of description as to what would have caused this.

I would like to find out where this information is logged, so I can create a job that looks for messages like this to alert users of a failed report.

I would also like to know where the logs are in case I do need to troubleshoot something vague like this.

I really would appreciate any help :)

Answer 1

You sometimes see this error when the report is cancelled due to a timeout. You can check the current timeout setting in Report Manager > Site Settings.

Answer 2

I'm not 100% sure but you might try and query the execution log tables in the SSRS database. It should tell you everything about the execution of the report and the failure of the subscription I would thin.

Answer 3

The below basic query may help I run it every 24h. It was a quick and dirty. Keep in mind it will only report the last failure so if your report schedule is less than daily (say every two hours) and the last run worked a previous failure for that subscription may be missed.

declare @Temp table(
id int IDENTITY(1,1) NOT NULL,
TextMessage NVARCHAR(MAX)
)

insert into @Temp
SELECT 
    '<b>Name: </b>'                 + [Name]                + '</br>' +
        '<b>Path: </b>'                 + [Path]                + '</br>' +
    '<b>Last Modified: </b>'        + 
    cast(Sub.[ModifiedDate] as varchar(20)) + '</br>' +
        '<b>Failure Description: </b>'  + [LastStatus]          + '</br>' +
        '<b>Subscription Type: </b>'    + [EventType]           + '</br>' +
        '<b>Last Run Time: </b>'        + 
                    cast([LastRunTime] as varchar(20))      +  '</br>'
FROM [ReportServer].[dbo].[Subscriptions] as Sub
left outer join [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
where LastStatus like 'Failure%'

declare @tableHTML NVARCHAR(MAX) = '<h2><u>SSRS Report Subscription Failure Notification</h2></u></br>' 



declare @Count int = (select count(*)
FROM @Temp)

while @Count > 0 --loop through each failed report
BEGIN

select @tableHTML = @tableHTML + '</br>' + TextMessage + '--------------------------------------------------------------------------------</br>' from @Temp where id = @Count 


set @Count -=1
end


--Email 
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Your Profile',
        @from_Address = 'Your FromEmail Address',
        @recipients = 'Your Recipents;',
        @subject = 'SSRS Report Failure Notification ',
        @body = @tableHTML,
            @body_format = 'HTML' ; 

Answer 4

Make sure your subscription "owner" account isn't locked or has its password expired.