Reporting Services – Using Alternate name for ReportServer

 

Background

As always outsmarted myself, decided to use names other than the defaults of ReportServer and ReportServerTempDB while restoring the backup files supplied by the Vendor.

Restored the database and removed the encryption keys.

Took the default ReportServer and ReportServerTempDB offline and got a good error that indicated to me that RS is trying to access the ReportServerTempDB.

Error

Here is the error
Error Image

database-reportservertempdb-cannot-be-opene

Error Text
 
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 Database 'ReportServerTempDB' cannot be opened because it is offline. 
 
 

Code

To fix took to the Net and found a very, very helpful SQL from Claude Haeberli.

Claude’s profile is here and his code is here.

 

Revised Code

Modified the code and made a SP out of it.

Here is the revised code.



use master
go

if object_id('[dbo].[sp_ChangeReferencedDatabaseName]') is null
begin

	exec('create procedure [dbo].[sp_ChangeReferencedDatabaseName] as ')

end
go


alter procedure [dbo].[sp_ChangeReferencedDatabaseName] 
(
	  @dbName sysname
	, @dbNameRevised sysname
	, @specificObject sysname = null
)
as 
begin

	set nocount on;
	set XACT_ABORT on;

	declare @tblObject TABLE
	(
		  [id]		int not null identity(1,1)
		, [schema]  sysname not null
		, [object]  sysname not null
		, [definition] varchar(max) not null
		, [definitionLen] int not null
	)

	declare @id			   int
	declare @idMax		   int
	declare @schema		   sysname
	declare @object		   sysname
	declare @definition    varchar(max)
	declare @definitionLen int 

	declare @definitionRevised varchar(max)

	declare @log		   varchar(max)

	declare @CHAR_NEWLINE		 varchar(10)
	declare @SQLBatchSeparator	 varchar(10)
	declare @SET_STATEMENTS		 varchar(300);

	set		@CHAR_NEWLINE = char(13) + char(10);
	set     @SET_STATEMENTS = 'SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;'
	set     @SQLBatchSeparator = 'go';

	insert into @tblObject 
	(
		  [schema]
		, [object] 
		, [definition] 
		, [definitionLen] 
	)
	select 
			  tblSS.[name]
			, tblSO.[name]
			, tblSSM.[definition]
			, [definitionLen]
				= len(tblSSM.[definition])

	from   sys.objects tblSO

	inner join sys.schemas tblSS

			on tblSO.schema_id = tblSS.schema_id

	inner join sys.sql_modules tblSSM

			on tblSO.object_id = tblSSM.object_id

	where   CHARINDEX
			(
				   @dbName
				, [definition] 
			)  > 1

	and 
			(
				   ( @specificObject is null )
				or ( tblSO.object_id = object_id(@specificObject) )
			)

	order by
			tblSO.[type]

	set @id = 1

	set @idMax = ( select max([id]) from @tblObject)

	while (@id <= @idMax)
	begin

		select
				  @schema = [schema]
				, @object = [object]
				, @definition = [definition]
				, @definitionLen = [definitionLen]

		from @tblObject		 

		where [id] = @id

		set @definitionRevised = @definition

		set @definitionRevised = replace(@definitionRevised, 'CREATE PROC', 'ALTER PROC')

		set @definitionRevised = replace(@definitionRevised, 'CREATE VIEW', 'ALTER VIEW')
		
		set @definitionRevised = replace(@definitionRevised, 'CREATE FUNCTION', 'ALTER FUNCTION')

		set @definitionRevised = replace(@definitionRevised, 'CREATE TRIGGER', 'ALTER TRIGGER')

		set @definitionRevised = replace(@definitionRevised,  @dbName,  @dbNameRevised)

		set @definitionRevised =  ''
								   + @definitionRevised
								   ;
			 
		set @log = rtrim(cast(@id as varchar(6)))
						+ ') '
						+ '{' 
						+ @definitionRevised 
						+ '}'


		print @log

		exec(@definitionRevised)

		set @id = @id + 1

	end


end

go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_ChangeReferencedDatabaseName]'
go

 

 

Sample


use [master]
go

use [ReportServer.Vendor]
go

declare	  @dbName sysname
declare   @dbNameRevised sysname
declare   @specificObject sysname 

set @dbName = 'ReportServerTempDB'
set @dbName = 'ReportServerTempDB'

set @dbNameRevised = 'ReportServer.VendorTempDB'

--set @specificObject = '[dbo].[ExtendedCatalog]'

begin tran


	exec [dbo].[sp_ChangeReferencedDatabaseName]
			  @dbName = @dbName
			, @dbNameRevised = @dbNameRevised
			, @specificObject = @specificObject


commit tran

Source Code Version Control

GitHub

Here is the GitHub Repository

 

 

Connect

  1. Rename ReportServer & ReportServerTempDb databases – by AMtwo
    • Connect ID :- 2986754
    • Date Opened :- 7/28/2016 2:41:02 PM
    • Status :- Active
    • Type :- Suggestion

 

Leave a Reply

Please log in using one of these methods to post your comment:

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