Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


renamed ReportServerTEMPDB now errors referencing dbo.executioncache


renamed ReportServerTEMPDB now errors referencing dbo.executioncache

Author
Message
Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
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 Angry ). 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.
Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
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.
Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
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.
dirnberger
dirnberger
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
WSquared
WSquared
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1368 Visits: 286
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"



Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
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.
WSquared
WSquared
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1368 Visits: 286
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



Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
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.
vmb2000_uk
vmb2000_uk
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.

Smile 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
tim.rutte
tim.rutte
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search