13.07.2017

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:

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.

  1. USE [ReportServerNative2014_NewName2]
  2. GO
  3. DROP TRIGGER [dbo].[Schedule_UpdateExpiration]
  4. GO
  5. CREATE TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]  
  6. AFTER UPDATE
  7. AS
  8. UPDATE
  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.

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