-
RSConfigTool can't upgrade the database named as *ReportServer
-
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指令再執行即可。
-
SSRS安裝之過五關斬六將
-
今天要在一台Domain Controller Windows 2000上安裝一台SQL Server Reporting Service 2005(SSRS)，一路過五關斬六將，還跟Bug博鬥到三更半夜。安裝完成時不禁喜極而泣，特PO文紀念。
註: 該主機上已安裝了SQL 2000 Reporting Service, 故SSRS未裝在Default Instance，兩個網站則命名為/SSRSReports及/SSRSReportServer。
【東岭】Reporting Services Configuration Manager要建立資料庫時，出現Invalid object name 'sysdatabases'的錯誤訊息。
【解決】確定你填寫用來登入SQL的帳號Default Database是'master'。這Bug在SQL 2005 SP1後已經修掉了。Forum Post
【洛陽】無法啟動ReportServer，出現以下訊息...
Source:SQL Server Report Service, EventID=0
MyServer 無法啟動服務。Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: 無法辨識使用者或群組名稱 'MyServer\ASPNET'。
於 Microsoft.ReportingServices.Library.Native.NameToSid(String name)
於 Microsoft.ReportingServices.Library.ServiceAppDomainController.StartRPCServer(Boolean firstTime)
於 Microsoft.ReportingServices.Library.ServiceAppDomainController.Start(Boolean firstTime)
於 Microsoft.ReportingServices.NTService.ReportService.OnStart(String[] args)
於 System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)
Source:Report Server Windows Service (SSRS) , EventID=121
The Remote Procedure Call (RPC) service failed to start.
【解決】MyServer是台Windows 2000 Server且為Domain Controller，故要依MS KB 911846，將WebSrviceAccount設成IWAM_MyServer。
【汜水關】http:// MyServer /SSRSReports無法啟動，在Log中出現
Source: Report Manager, Category: Logging, EventID=111
Report Manager cannot create the trace log C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles\ReportServerWebApp__05_20_2008_15_58_10.log. http:// MyServer /SSRSReportServer無法使用，提示存取rsreportserver.config權限不足。
【解決】賦與IWAM_MyServer對LogFiles及..../ReportServer/rsreportserver.config的存取權限。
【滎陽】執行http:// MyServer /SSRSReports 出現The ReportServerVirtualDirectory element is missing錯誤
【解決】修改C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\RSWebApplication.config，加上ReportServerVirtualDirectory 設定。MS KB
【滑州】執行http:// MyServer /SSRSReports出現HTTP Status 400: Bad Request
【解決】原來是在之前設定ReportServerVirtualDirectory 時，輸入成完整的URL，其實只需要給/SSRSReportServer即可。
===== 我是分隔線 =====
不過，說老實話前面提的這些都只是小菜而已，靠經驗或Goggle一下就迎刃而解，真正困住我的大魔王是SQL 2005 SP2的一個Bug。這部分留待下一篇再說明。