subscription after 2005-2008 migration
No showing under sqlagent jobs, they do seem to be in the migrated db. the subscriptions are shown within report manager and I can create a new one and it will be created and shown in sqlagent. BUT, when I click OK on the report delivery
option on eather an existing or new subscrition I get this error: which is the old 2005 reportservertempdb
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)
Get Online Help
Invalid object name 'ReportServer2005TempDB.dbo.ExecutionCache'.
Thanks in advance,
Fred
July 5th, 2011 9:03am
Hi FredEStover,
It seems that the problem with requiring the old temp database. Generally, there is a trigger on the Schedule table in ReportServer database and you can see that it may hard codes the ReportServer2005TempDB database.
This may be a problem with your update scripts in that they do not update the trigger when you migrate a existing database. You can view the code by running follows,
use [YOUR REPORTSERVER DATABASE NAME]
dbo.sp_helptext Schedule_UpdateExpiration
You can change the trigger to reference the correct tempdb as follows
ALTER TRIGGER [dbo].[Schedule_UpdateExpiration] ON [dbo].[Schedule]
AFTER UPDATE
AS
UPDATE
EC
SET
AbsoluteExpiration = I.NextRunTime
FROM
[YOUR REPORTSERVER TEMP DB NAME].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
and run it.
Let me know if that works for you.
Thanks,
Eileen
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2011 11:49am