Gotcha on named instance migration for SSRS
You have to migrate a SSRS named instance to a new server in
parallel with the existing SSRS Server.
Not an uncommon scenario when in production and bringing up a new
server, test and validate the new environment, and need to minimize potential
downtime. So you bring up the new server,
backup the SSRS repository databases, and restore on the new server, assign the
current service account to new service, restore the encryption key, and then
Report Manager failed on an internal error. You get an error message: “An error occurred within the report server
database. This may be due to a connection failure, timeout or low disk
condition within the database. (rsReportServerDatabaseError) Get Online Help
Invalid object name ‘ReportServerTempDB.dbo.TempCatalog‘. Could not use view or
function ‘ExtendedCatalog’ because of binding errors”. What happened? When installed,
SSRS will actually hardcode the name of the ReportServerTempDB database to
stored procedures and functions in the ReportServer database. The version of SSRS being used does not matter. Approximately 72 stored procedures and 2
functions have a hard coded reference embedded internally in SSRS. Not a good feeling when at a client and then
had multiple named instances being migrated in each DEV, TEST, and PROD
environments.
Here is a query against the metadata view to see the full
impact:
USE [ReportServer]
GO
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%reportservertempdb%'
GO
Here is a code snippet from the stored procedure [dbo].[AddDataSet]
:
IF(@EditSessionID is not null)
BEGIN
INSERT
INTO [ReportServerTempDB].dbo.TempDataSets
(ID,
ItemID, [Name], LinkID)
VALUES
(@ID,
@ItemID, @Name, @ActualLinkID)
…
To fix, you have to modify the reference. Script out all the stored procedures and
functions from the migrated database with a drop and create, perform a global
replace the current to the proper name of the ReportServerTempDB database, and
then execute the script. Restart the
service and you should be fine.