You get this error in SQL Server Reporting Services 2005:
Unhandled exception caught in Scheduling maintenance thread: System.Data.SqlClient.SqlException: Invalid object name 'ReportsTempDB.dbo.ExecutionCache'.
Also, any scheduled report fails with an rsInternalError status. If you've changed your report database recently (say while upgrading from a previous version of SSRS), Microsoft missed updating the code in one trigger. You need to manually alter the trigger code as follows:
ALTER TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]
AFTER UPDATE
AS
UPDATE
EC
SET
AbsoluteExpiration = I.NextRunTime
FROM
[ReportsTempDB].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
To implement the fix, just replace the "[ReportsTempDB]" with the name of your temp database and execute the code.
Subscribe