<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Business Intelligence
»
renamed ReportServerTEMPDB now errors...
12 posts, Page 1 of 2
1
2
»»
renamed ReportServerTEMPDB now errors referencing dbo.executioncache
Rate Topic
Display Mode
Topic Options
Author
Message
Jane Matheson-154829
Jane Matheson-154829
Posted Friday, August 15, 2008 2:01 PM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, December 05, 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
Jane Matheson-154829
Jane Matheson-154829
Posted Friday, August 15, 2008 2:14 PM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, December 05, 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
Jane Matheson-154829
Jane Matheson-154829
Posted Friday, August 15, 2008 4:09 PM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, December 05, 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
dirnberger
dirnberger
Posted Tuesday, September 09, 2008 2:35 PM
Forum 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
WSquared
WSquared
Posted Friday, March 06, 2009 1:42 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 1,195,
Visits: 227
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
Jane Matheson-154829
Jane Matheson-154829
Posted Friday, March 06, 2009 7:03 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, December 05, 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
WSquared
WSquared
Posted Friday, March 06, 2009 7:33 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 1,195,
Visits: 227
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
Jane Matheson-154829
Jane Matheson-154829
Posted Friday, March 06, 2009 8:09 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, December 05, 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
vmb2000_uk
vmb2000_uk
Posted Thursday, September 10, 2009 8:50 AM
Forum 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
tim.rutte
tim.rutte
Posted Monday, August 16, 2010 9:34 AM
Forum 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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
