Interactive sorting in Reporting service fails with rsReportNotReady exception

Recently i came across an interesting issue where the Interactive sorting in reports fail with the following exception,

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

Interestingly after much of the scratches and troubleshooting, we looked at the database level setting by running the following command,

select * from sys.databases

And obsever the columns,

snapshot_isolation_state_desc

is_read_committed_snapshot_on

for report server and report server tempdb databases.

If the values shows ON and 1 for the respective column values, then there lies the problem.

By default these values are OFF and 0 for these databases.

So you need to change them back to the default values by running the following SQL scripts,

 Now try running the report and check the Interactive sorting. This should fix the issue.

ALTER DATABASE ReportServer

SET ALLOW_SNAPSHOT_ISOLATION OFF

 

ALTER DATABASE ReportServerTempDB

SET ALLOW_SNAPSHOT_ISOLATION OFF

 

ALTER DATABASE ReportServer

SET READ_COMMITTED_SNAPSHOT OFF

 

ALTER DATABASE ReportServerTempDB

SET READ_COMMITTED_SNAPSHOT OFF

 

 

 HTH!

Selva.

[All the posts are AS-IS and doesn't carry any warranty]