Bug in SSRS Upgrade Script from SQL Server 2014 to SQL Server 2016

Technical Value

SQL Server SSRS

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:

Error--message

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.

USE [ReportServerNative2014_NewName2]
GO
DROP TRIGGER [dbo].[Schedule_UpdateExpiration]
GO
CREATE TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]
AFTER UPDATE
AS
UPDATE
EC
SET
AbsoluteExpiration = I.NextRunTime
FROM
[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
GO
ALTER TABLE [dbo].[Schedule] ENABLE TRIGGER [Schedule_UpdateExpiration]
GO