March 23, 2009 at 1:50 pm
Recently, we migrated two instances of RS 2005 to RS 2008. In one of the instances we encounter the following error when clicking 'OK' to create/modify a report subscription.
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 'ReportServerTESTTempDB.dbo.ExecutionCache'.
Note: We have change the name of this instance. Thus, the object referred to above, is from the 2005 database. Whatever action is occuring to use this object, it should be looking for 'ReportServerStagingTempDB.dbo.ExecutionCache'
Was it a mistake to change the names? How might we be able to resolve this?
I'm more than willing to provide more information if necessary. Any help provided will be much appreciated. I thank you in advance.
Joey
March 26, 2009 at 2:42 pm
We found the root cause of the problem and a solution.
There exists a trigger, Schedule_UpdateExpiration, on the ReportServer.Schedule table that references the "TempDB" dbo.ExecutionCache table. We altered the trigger to point to the table with the appropriate fully qualified name.
So far we have yet to notice any other objects that point to the old db name, thus everything is working properly. However, we will keep our fingers crossed.
The thread at the following link was a big help.
http://www.sqlservercentral.com/Forums/Topic553765-147-1.aspx#bm670251
April 1, 2009 at 2:41 pm
Thanks Soo Much. I had a similar issue and got around it using the above approach
September 20, 2010 at 6:37 pm
Excellent!!!!
I had the same issue and just found this thread. I changed the name in the trigger to reference the new server and I am back in business.
Things will work out. Get back up, change some parameters and recode.
December 13, 2010 at 8:20 am
Frustrating. Reading through this thread a smile grew on my face as I realised I had found the resolution to my issues.
Alas not. I have exactly the same problem as those mentioned here, but not the same reason. I have not changed instance names etc. Indeed this has occured on a deployment that has been working for months before. I left to go on paternity leave for 2 weeks, returned to find that I could not amend any subscriptions, and whilst the job in SA appeared to be running properly, no reports were being emailed out.
I get the same message as others here. I did check, for happenstance, to see if my triggers had changed, but mine do reference the correct locations etc.
I don't hold out much hope, but if anyone can help with that i'd be eternally greatful.
thanks.
December 23, 2010 at 2:52 am
Hi There,
We had a similar problem and when we analyzed the issue we found that there were two different SQL Agent Jobs with the same name assiciated with the same schedule. When we deleted those SQL Agent Jobs & Re-Created the Schedule, it solved the problem.
Hope it helps!
October 15, 2011 at 7:49 am
Thanks. The database on our 2005 server was RSDB and RSDBTemp, and we renamed the ReportServer and ReportServerTempDB on 2008, and we got this problem. It worked. Thanks!
April 4, 2012 at 10:38 am
Thanks so much!!!
September 9, 2013 at 10:08 am
Completely different cause and resolution to this problem for me:
When I grant sysadmin to my SSRS login it resolves the issue (obviously not a solution). Double-triple-quadruple checked all RSExec role permissions. All correct. Grasping at straws for hours. Finally noticed the ownership on all of the subscription jobs was odd. It was owned by a domain user account that no longer exists.
Several weeks ago we had switched the service account for SSRS to a new AD account (new naming convention, old account deleted). I cannot find any documentation from MS regarding updating ownership of subscription / jobs when modifying the SSRS Service account. Regardless, changing the ownership to = the new AD account resolves the issue.
Seems to me, this is a deficiency in SSRS config manager or to say the least, lacking / undocumented feature. Maybe I'm just a silly DBA and this should be a no-brainer. Seems to me when you log into the config manager and change the user account, you should be forced to do this via a dbo / sys admin user on the SQL Server. It should then automatically change the ownership for you. :angry:
March 24, 2015 at 5:48 am
Did you set the account you want to use to set the subscription in Reporting Services Configurations Manager, if not do so. It worked for me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply