August 15, 2008 at 2:01 pm
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.
August 15, 2008 at 2:14 pm
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.
August 15, 2008 at 4:09 pm
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.
September 9, 2008 at 2:35 pm
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
March 6, 2009 at 1:42 am
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"
March 6, 2009 at 7:03 am
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.
March 6, 2009 at 7:33 am
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
March 6, 2009 at 8:09 am
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.
September 10, 2009 at 8:50 am
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
August 16, 2010 at 9:34 am
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...
December 19, 2012 at 8:01 am
Thanks for the solution,
Update 'the evil trigger' saved me a lot.
January 24, 2013 at 4:08 am
hi, thank you very much. The solution saved me a lot of time. I had the same problem, but i have had to changed only the new temp db name in the mentioned trigger of the schedule table. All stored procedures had the right temp db name. Beforehand i configured the database over the Reporting Server Configuration tool again.
Many thanks again.
regards
Jürgen
May 2, 2016 at 12:28 pm
Trigger change worked for me. Thanks!!
Francis
-----------------
SQLRanger.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply