Bug in SSRS Upgrade Script from SQL Server 2014 to SQL Server 2016
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:
On the server where the reporting services are installed we found the following in the error event log:
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.
- USE [ReportServerNative2014_NewName2]
- DROP TRIGGER [dbo].[Schedule_UpdateExpiration]
- CREATE TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]
- AFTER UPDATE
- AbsoluteExpiration = I.NextRunTime
- [ReportServerNative2014TempDB].dbo.ExecutionCache AS EC
- INNER JOIN ReportSchedule AS RS ON EC.ReportID = RS.ReportID
- INNER JOIN inserted AS I ON RS.ScheduleID = I.ScheduleID AND RS.ReportAction = 3
- ALTER TABLE [dbo].[Schedule] ENABLE TRIGGER [Schedule_UpdateExpiration]
I reported this issue to Microsoft.
So be careful with the upgrade, but in general it is working fine.