RSConfigTool can't upgrade the database named as *ReportServer
3 |
I found a bug about Reporting Services Configuration Tool. Here's the detail:
- I installed SSRS as non-default instance.
- Before configurating reporting service instance, I installed SQL 2005 SP2.
- Then I used Reporting Services Configuration Manager(RSConfigTool.exe) to configure my reporting service instance. In "Databases Setup" tab, I created a new database named as 'SSRSReportServer'.
- After database was created, I tried to apply the setting, UI prompted for upgrading database from version 'C.0.8.40' to the newest version.
- While database upgrading, I got a exception:
Could not locate entry in sysdatabases for database 'SSRS'. No entry found with that name. Make sure that the name is entered correctly. - Startup SQL Profiler, I caught this SQL Script
USE SSRS[SSRSReportServerTempDB]
--------------------------------------
-- T.0.8.40 to T.0.8.41
--------------------------------------
-- No change in tables
--------------------------------------
-- T.0.8.41 to T.0.8.42
--------------------------------------
if (select count(*) from dbo.syscolumns where id = object_id('SessionData')
and name = 'ExecutionType') = 1begin
ALTER TABLE [dbo].[SessionData] DROP COLUMN [ExecutionType]
end
- The first USE SSRS[SSRSReportServerTempDB] should be USE [SSRSReportServerTempDB].
- Finally, I tried WMI Provider "\root\Microsoft\SqlServer\ReportServer\v9\admin:MSReportServer_ConfigurationSetting" [GenerateDatabaseUpgradeScript] method, it returned "USE SSRS[SSRSReportServerTempDB]", the same as I saw in RSConfigTool. But if the database name is ReportServerXX, then USE ReportServerXXTempDB is returned. It seems the issue happened only when database name LIKE '%ReportServer'
Resolution:
I think the Reporting Service WMI Provider has a bug in report service database name to temporary database name conversion, when database name like '%ReportServer'. As we know this, we can keep away from this bug easily by avoiding naming database as *ReportServer. (But it took me several hours to found out this truth...)
[中文摘要]
在設定SSRS的資料庫時,若你是先安裝SQL 2005 SP2後才設定Reporting Service Instance,且剛好資料庫又命名為*ReportServer(不包含預設的"ReportServer",而是指"BlahReportServer"、"BooReportServer"這種格式),則會在設定過程中遇到資料庫升級失敗。經過一番測試,確認此為Reporting Service WMI Provider的Bug。最簡單的解決方法是為DB換個名字,不要跟它拼命,要是一定要取這種名字,你可以用RSConfigTool工具裡產生Script的功能,將升級資料庫Script先寫成檔案後手動改掉不正確的USE dbname指令再執行即可。
Comments
# by Jasper
請問黑暗大,您程式的部份,都有顏色標記的功能,若運用到其他Blog,要如何實作呢,是否您自己有針對這個功能寫一個小型編輯器,使其能套用CodeBlock 上的CSS Tag呢?
# by Jeffrey
To Jasper, 你有用Live Writer在寫Blog嗎? 我的朋友雞老大寫了一個為程式加顏色的Plug-In, 你可以參考看看。 http://columns.chicken-house.net/blogs/chicken/archive/2008/04/04/3151.aspx
# by Jasper
to Jeffery,我不道什麼是Live Writer,我的Blog是BlogSport,都是採用線上編寫的方式.謝謝您解惑. 而雞老大的Blog我稍後會去參考...感恩 還有,看您的Blog以來,都忘了和你說聲謝謝...您的Blog豐富了我的視野...真的相當相當受用...感恩...