pureVirtual Services

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.

Because the database mail service is disabled after 10 minutes by default in SQL Server 2005, you may want to run this script to check if the service is enabled, and if not, start it.

IF NOT EXISTS 
(
 SELECT 1 FROM msdb.sys.service_queues
 WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1
)
-- Enable the database mail service
EXEC msdb.dbo.sysmail_start_sp