SSRS Reports Issue after Migration: Invalid object name ‘ReportServerTempDB.dbo.TempCatalog’   4 comments


I recently migrated one of our SQL Server Database Engine and SSRS instances from SQL Server 2008 to SQL Server 2014. Post migration, when I tried to browse the SSRS reports, I get this error:

Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: 
An error occurred within the report server database.  
This may be due to a connection failure, timeout or low disk condition within the database
. ---> System.Data.SqlClient.SqlException: 
Invalid object name 'ReportServerTempDB.dbo.TempCatalog'.
Could not use view or function 'ExtendedCatalog' because of binding errors.

There are quite a few articles over internet which suggests different cause and fix. However, none of them seemed to work for me as I had neither changed the report server databases name, no triggers or objects were modified in the original RS database. Digging deeper, I found that there are a few objects e.g. TempCatalog, Datasets etc. was missing from the current ReportServerTempDB database. A possible case of corruption?

Well, I knew I certainly had to retain my ReportServer database let ReportServerTempDB be rebuilt afresh. So, re-created a SQL Server 2014 version of ReportServerTempDB while retaining the original SQL Server 2008 version of ReportServer database to fix the issue. Here are the steps I followed on the migrated server (SQL 2014):

Disclaimer: The above steps might not be supported. They have been provide as is as it worked in my environment. Please follow the steps at your own risk. DO NOT TRY THIS on a Production box, have backup for everything even when you try on a Test box.

  1. Stop SSRS from Reporting Services Configuration Manager.
  2. Open SSMS, connect to SQL Database Engine. Detach ReportServer and ReportServerTempDB.
  3. Stop SQL Server services.
  4. Rename physical files (data and log) of ReportServer and ReportServerTempDB by prefixing Orig_ (stands for Original).
  5. Restart SQL Server services. You wouldn’t find ReportServer and ReportServerTempDB databases now as we detached them in Step-2 above.
  6. Restart SSRS from Reporting Services Configuration Manager, if stopped.
  7. Open Reporting Services Configuration Manager. Create new reporting databases from Configuration Manager.
  8. Stop SSRS from Reporting Services Configuration Manager.
  9. From SSMS, connect to SQL Database Engine. Detach ReportServer database ONLY as we have to replace it with the original one.
  10. Stop SQL Server services.
  11. Rename physical data and log files for current (newly created) ReportServer database prefixing New_.
  12. Rename back original ReportServer database files (recall step-4 where we had renamed it prefixing Orig_) removing Orig_.
  13. Start SQL Server services.
  14. Open SSMS, attached original ReportServer database.
  15. Restart SSRS from Reporting Services Configuration Manager, if stopped.
  16. Try to browse through report. It should be working fine now.

Hope this helps!

Stay tuned for more interesting blog posts.

4 responses to “SSRS Reports Issue after Migration: Invalid object name ‘ReportServerTempDB.dbo.TempCatalog’

Subscribe to comments with RSS.

  1. wow, this worked perfectly for me! Thank you so much

  2. Thanks! It helped alot!

  3. Extremely useful blog. Thumbs up!!

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: