Today instead of the beautiful information our reports displayed this ugly error
- An error has occurred during report processing. (rsProcessingAborted)
- Query execution failed for dataset ‘StoredProcedure’. (rsErrorExecutingCommand)
- For more information about this error navigate to the report server on the local server machine, or enable remote errors
If we had access to the report server machine this was easy to troubleshoot. But we don’t have access to the report server. We needed another approach.
Image courtesy of jesadaphorn / FreeDigitalPhotos.net
Looking through the changes from last night we noticed a small performance enhancement. In a stored procedure that prepares the data for the reports we added the following line:
UPDATE STATISTICS [dbo].[Fact]
In the Microsoft documentation it says you’ll need alter table permission to use update statistics. So we added another grant for the reporting service account.
GRANT EXECUTE ON [dbo].[proc_prepare4reporting] TO [SSRS] GRANT ALTER ON [dbo].[Fact] TO [SSRS]
After the deployment the reports showed the beautiful information again.