Stefan Grigat

I found a bug in the SSRS upgrade script SQL Server 2016 which I will describe here also including a workaround! :-)
In my last blog ( SSRS Upgrade from SQL Server 2014 to SQL Server 2016 – Script when database renamed ) I described what happens with the Report Server Database during the version upgrade.

After the migration we used the new version, but when adding a new subscription to a report we got an error from the report manager:

An error has occurred

On the server where the reporting services are installed we found the following in the error event log:

ERROR: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException:
An error occurred within the report server database. This may be due to a connection failure,
timeout or low disk condition within the database.
—> System.Data.SqlClient.SqlException: Invalid object name ‚ReportServerNative2014TempDB.dbo.ExecutionCache‘.

So there was a hint, a link to the ReportServerTempDb with the old name which we had renamed.
While investigating with the DBA on customer site we found a trigger „[dbo].[Schedule_UpdateExpiration] “ on the table „[dbo].[Schedule]“ which has a reference to the Report Server TempDb but was not adjusted by the upgrade script.

We recreated the trigger and the subscription worked fine.

I reported this issue to Microsoft.
So be careful with the upgrade, but in general it is working fine.