You have restored your databases, you renamed them if needed (in my case ReportServer and ReportServerTempDB from ReportServer$Instance and ReportServer$InstanceTempDB), jobs, everything, when migrating SSRS to a new server. Yet, scheduled subscriptions still not working (oddly enough, ad-hoc ones were working, could browse reports, etc.). In my case, when I tried editing Subscriptions, I hit this error: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. In my particular case, I checked the logs at \Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles and found this error burried in the logs: 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. ---> Microsoft.Data.SqlClient.SqlException: Invalid object name 'ReportServer$InstanceTempDB.dbo.ExecutionCache‘ – in other words, something is still trying to use the old database names.
Blind try:SELECT OBJECT_NAME(id) FROM syscomments
WHERE text LIKE '%ReportServer$InstanceTempDB%'
Result: Schedule_UpdateExpiration. Turns out there is a trigger on the ReportServer - dbo.Schedule table that runs the following code:
UPDATE
EC
SET
AbsoluteExpiration = I.NextRunTime
FROM
[ReportServerTempDB$Instance].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
Once I replaced the hard-coded old database name with the new database name (ReportServerTempDB), it started working.
Again, this is a very quick blog post, but to summarize:
- ad-hoc reports were working
- scheduled reports were not working
- tried to edit a schedule from the /Reports/manage in the web browser – failed with the above error
- changed the hard-coded value in the trigger, everything started working again.
SQL-PLAY 