Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

renamed ReportServerTEMPDB now errors referencing dbo.executioncache Expand / Collapse
Author
Message
Posted Friday, August 15, 2008 2:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
I know what has caused my issue, it but don't know the solution. I'll explain my issue and hope someone can point me to a solution.

I am testing an upgrade of reporting services 2000 to 2005. I installed 2005 on a new dev server (both instances default instances) and told it not to configure. I restored my databases from the production server. BUT, when I did the upgrade I ran into serious issues with an installation bug. My databases were named NFSRSReportServer and NFSRSReportServerTempDB. The installation wouldn't upgrade these (error in script) because the database names end in the default names but it's not the entire default name for the databases (I'm not kidding ). SO I had to rename the databases. New names are NFSRS2005 and NFSRS2005TempDB. My understanding is that if I didn't rename them I would have errors on every SP I installed and would have to edit scripts to make it see the real names.

After restoring the databases by a different name, going through the upgrade, installing the encryption key, I am having problems with subscriptions. When I try to edit subscriptions it fails (the subscriptions are also not working and failing with the same error when they try to run in agent) with the error:

System.Data.SqlClient.SqlException: Invalid object name 'NFSRSReportServerTempDB.dbo.ExecutionCache'.

It's using my old database name (highlighted above), not the new one - NFSRS2005TempDB

Can someone tell me how to tell RS what the correct name for ReportServerTEMPDB is?
Based on responses from post
https://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3644560&SiteID=1
It appears the data is stored in the rsreportserver.config - but encrypted.
Post #553765
Posted Friday, August 15, 2008 2:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
OK, I found this:

http://www.sqlservercentral.com/Forums/Topic553765-147-1.aspx

which includes this:

"Renaming a report server database is not supported because the report server databases are considered internal components. Renaming the report server databases causes errors to occur. Specifically, if you rename the primary database, an error message explains that the database names are out of sync. If you rename the ReportServerTempdb database, the following internal error occurs later when you run reports:

"An internal error occurred on the report server. See the error log for more details. (rsInternalError)

Invalid object name 'ReportServerTempDB.dbo.PersistedStream'."

This error occurs because the ReportServerTempdb name is stored internally and used by stored procedures to perform internal operations. Renaming the temporary database will prevent the stored procedures from working properly. "

But, WHAT AM I SPPOSED TO DO? I can't upgrade cleanly because of the name I originally chose for my chose DB and I can't rename it either?

Has anyone else faced this issue? What did you do? I'm pretty late in this game on the upgrade, certainly there's some experience out there on this.

Post #553777
Posted Friday, August 15, 2008 4:09 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
After much consternation, I have found a trigger referencing the invalid object. Trigger [Schedule_UpdateExpiration] on ReportServer table Schedule has the offending reference in it. In test, I altered this trigger to reference the correct report server tempdb and now subscriptions appear to be working properly. So far I have found nothing else broken.

Any comments from anyone? If I'm going through this name change - maybe I should use the default names if MS isn't going to support very well anything else.
Post #553834
Posted Tuesday, September 9, 2008 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:51 AM
Points: 1, Visits: 2
I have the same issue, and changing the trigger that you mentioned did not resolve it. I stopped and started the reporting services and did an iisreset.

I moved the reporting services DBs from the default sql instance on one server to a named reporting instance on another. I can list the report definitions but whenever I try to run one it comes back with the same error, noting the invalid object with the wrong tempDB name (ReportServerTempDB instead of ReportServer$RPTTempDB).

Any help from anyone would be appreciated.
LD
Post #566481
Posted Friday, March 6, 2009 1:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:01 AM
Points: 1,351, Visits: 272
Have just encountered same myself, after searching through the database, the aforementioned trigger is not the only place where the name of the temporary database is hard coded, it is in rather a lot of stored procedures, so have decided to pay attention to the documentation where it says that changing the names is not supported, and revert it back after all "a rose by any other name"


Post #669998
Posted Friday, March 6, 2009 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
Can you give any examples of where you found the DB name in the stored procedures. We've been running without error since the upgrade.
Post #670178
Posted Friday, March 6, 2009 7:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:01 AM
Points: 1,351, Visits: 272
Just realised, mine is 2008 which we are moving from a named instance to the default instance on the other machine, and thought we might rename the databases, but here's the list of sp's where it occurs

AddPersistedStream
AddReportToCache
CheckSessionLock
CleanBrokenSnapshots
CleanExpiredCache
CleanExpiredSessions
CleanOrphanedSnapshots
ClearSessionSnapshot
CopyChunks
CopyChunksOfType
CopyChunksOfType
CreateChunkAndGetPointer
CreateChunkSegment
CreateNewSnapshotVersion
CreateSegmentedChunk
CreateSession
DecreaseTransientSnapshotRefcount
DeepCopySegment
DeleteExpiredPersistedStreams
DeleteObject
DeleteOneChunk
DeletePersistedStream
DeletePersistedStreams
DeleteSnapshotAndChunks
DereferenceSessionSnapshot
FlushReportFromCache
GetChunkInformation
GetChunkPointerAndLength
GetFirstPortionPersistedStream
GetNextPortionPersistedStream
GetReportForExecution
GetSessionData
GetSessionData
GetSnapshotChunks
GetSnapshotPromotedInfo
IncreaseTransientSnapshotRefcount
InsertUnreferencedSnapshot
IsSegmentedChunk
LockPersistedStream
LockSnapshotForUpgrade
MarkSnapshotAsDependentOnUser
OpenSegmentedChunk
PromoteSnapshotInfo
ReadChunkPortion
ReadChunkSegment
RemoveReportFromSession
RemoveSegment
RemoveSegmentedMapping
RemoveSegmentedMapping
SetPersistedStreamError
SetSessionCredentials
SetSessionData
SetSessionParameters
SetSnapshotChunksVersion
SetSnapshotProcessingFlags
ShallowCopyChunk
TempChunkExists
UpdateSnapshotPaginationInfo
UpdateSnapshotReferences
WriteChunkPortion
WriteChunkSegment
WriteFirstPortionPersistedStream
WriteLockSession
WriteNextPortionPersistedStream



Post #670205
Posted Friday, March 6, 2009 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
I've spot checked and these have the correct tempDB name. Since I don't have my old DB anymore to compare I have to assume one of two things. I found them and altered them and just don't remember it (or copied them fro an install using the default) ; or they were added after I complete my upgrade.
Post #670251
Posted Thursday, September 10, 2009 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 10, 2009 8:47 AM
Points: 1, Visits: 0
Thank you for your help guys,

I've been having the same problem and had to fix this.

If anyone is looking for a quick answer then here is what I did to solve my problem:

- Updated trigger on dbo.schedule to reference the correct tempdb.
- Scripted all stored procedures with their permissions onto a new query then "find and replaced" all instances of the old tempdb with the new one.

:) Done... it took a while trying to get to the bottom of this but in simple terms follow the above and it should work.

Vishal
Post #785746
Posted Monday, August 16, 2010 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 16, 2010 9:34 AM
Points: 1, Visits: 2
After much consternation, I have found a trigger referencing the invalid object. Trigger [Schedule_UpdateExpiration] on ReportServer table Schedule has the offending reference in it. In test, I altered this trigger to reference the correct report server tempdb and now subscriptions appear to be working properly. So far I have found nothing else broken.

Any comments from anyone? If I'm going through this name change - maybe I should use the default names if MS isn't going to support very well anything else.


Thanks!! Saved me a lot of time Exactly the right place...
Post #969897
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse