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

Technical Value

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:

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.

  1. USE [ReportServerNative2014_NewName2]
  2. GO
  3. DROP TRIGGER [dbo].[Schedule_UpdateExpiration]
  4. GO
  5. CREATE TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]  
  7. AS
  9.    EC
  10. SET
  11.    AbsoluteExpiration = I.NextRunTime
  12. FROM
  13.    [ReportServerNative2014TempDB].dbo.ExecutionCache AS EC
  14.    INNER JOIN ReportSchedule AS RS ON EC.ReportID = RS.ReportID
  15.    INNER JOIN inserted AS I ON RS.ScheduleID = I.ScheduleID AND RS.ReportAction = 3
  16. GO
  17. ALTER TABLE [dbo].[Schedule] ENABLE TRIGGER [Schedule_UpdateExpiration]
  18. GO

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

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden