Have you ever faced in a scenario where you are required to rename existing database. While dealing with one my client I have installed reporting server and during RS configuration I’ve chosen the default settings while creating the database so it created the reporting server dbname as ReportServer and ReportServerTempdb and RS working fine.Now here is the problem, as per the company policy the dbname should be in format ReportServer_Appname & ReportServer_AppnameTempdb (in my case app name is clix). This looks easy to me so I’ve just stopped reporting server and renamed both the databases as per their requirement and started reporting service. Now went to reporting server configuration tool (Check my article https://sql-articles.com/articles/dba/configuring-sql-server-reporting-services/ for configuring reporting server) and selected the new databases as shown below Wonderful no errors and RS is working fine when I browse the homepage so I handed over the server to client.
The actual problem lies here, now my client is running some rdl files to get the report however he is facing the error below while running the reports. I then jumped and tried to reconfigured RS with new db however none worked. So I just started digging it, later I found that all the procedures in the Reporting server database is now pointing to the old RS tempdb database as shown below
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
I felt that some objects are pointing to the old reportserver tempdb database so I gave a quick search on syscomments table on Reporting Server db (here it’s ReportingServer_Clix), now I found the culprit. It returned around 83 rows that means 83 procedures are pointing to the old report server tempdb as shown below
USE ReportServer_Clix GO SELECT OBJECT_NAME(id) 'Name' FROM syscomments WHERE text LIKE '%ReportServerTempDB%' ORDER BY Name
To resolve this, I have script all the procedures with drop existing parameter on report server database to a new query window. Now I did a search and replace the old report server tempdb name to new report server tempdb and recreated all the objects again as shown below.
235 occurences are there in the objects all of them is now replaced. Now I gone to reporting server and executed the rdl file, it worked perfectly fine as shown below
Thus to consolidate for renaming reporting server database , follow the steps below it will help you out.
- First and foremost thing, backup your existing reporting server databases
- Stop SQL Server reporting Services
- Now rename your report server database as required
- Now script stored procedures and triggers that exist on report server to a new file or query window. (Script with drop and create functionality)
- Use search and replace function , search for old report server tempdb database and replace with new report server tempdb database
- Now execute the modified script
- Start SQL Server reporting services
- Go to Reporting server configuration tool and go to database section
- From the drop down list select the new database (which is renamed) and click on Ok
That’s it your part is done. Reporting server databases are now renamed as per your requirement. I have tested the same in Denali CTP3, unfortunately this is the only version I have in my laptop.
Leave a Reply