SQL Server Configuration Manager relies on WMI calls to generate the database creation script. Configuration Manager does this by internally calling the
GenerateDatabaseCreationScript method. This works as expected, and the database is created correctly on the instance of SQL Server 2005. After this occurs, the SSRS service checks the database schema version during the service start. If it is required, the SSRS service then generates an update script whose purpose is to upgrade the database to the correct version.
This procedure currently fails on instances of SQL Server 2005 because of incorrect statements in the Transact-SQL script that is generated for the upgrade.
The upgrade script contains in-line value assignments in statements such as the following:
declare @maxCleanCount int = 200;
In-line assignment together with the declaration is not supported in SQL Server 2005.
To resolve this issue, generate the upgrade script manually, remove the unsupported statements, and then run the script manually to upgrade the database. You can do this by accessing the Reporting Services WMI Provider and then calling the appropriate methods for generating the script.
The detailed procedure for resolving this issue is as follows.
Step 1: Generate the creation script
Start Windows PowerShell, and then execute the following commands. Make sure that you replace any placeholders with the parameters that reflect your environment.
$RptSrv = gwmi -Namespace "root\Microsoft\SqlServer\ReportServer\<RS_INST>\v11\Admin" -Class "MSReportServer_ConfigurationSetting"
$Res = $RptSrv.GenerateDatabaseCreationScript("<ReportServer>", 0, 0)
$Res.Script | Set-Content "<c:\output\dbcreation2005.sql>"
Notes- The placeholder <RS_INST> represents the name of the instance of SSRS that is installed in your environment. To obtain this name if you don’t know it already, you can use the WBEMTest tool, connect to root\Microsoft\SqlServer\ReportServer, click Query, and then execute the following query:
Select * from __namespace
This will list the Reporting Services instances on your computer. You can then use the appropriate instance that corresponds to your SSRS 2012 service. - The placeholder <ReportServer> represents the name of the database that you want to create on the instance of SQL Server 2005.
- The placeholder <c:\output\dbcreation2005.sql> represents the full path of the output file that will contain the creation script.
Step 2: Generate the upgrade script
Use the same Windows PowerShell window that you opened in step 1 to execute the following two commands:
$Res = $RptSrv.GenerateDatabaseUpgradeScript("<ReportServer>","C.0.9.45")
$Res.Script | Set-Content "<c:\output\dbupgrade2005.sql>")
Notes- The placeholder <ReportServer> represents the name of the database that you want to create on the instance of SQL Server 2005. This should be the same name as the name that you used in step 1.
- The placeholder <c:\output\dbupgrade2005.sql> represents the output file that will contain the upgrade script.
Step 3: Run the creation script manually
- Start SQL Server Management Studio, and then connect to the SQL Server 2005 Database Engine that will host your ReportServer database.
- Open the script that you created in step 1.
- Execute the script to create the database.
Step 4: Change the upgrade script to remove the incorrect statements
Open the script that you generated in step 2 in a text editor of your choice, and then change the following statements as indicated in the following table.
| Original statement | New statement |
|---|
declare @maxCleanCount int = 200;
| declare @maxCleanCount int;
set @maxCleanCount = 200; |
declare @now as datetime = GETDATE();
| declare @now as datetime;
set @now = GETDATE(); |
declare @now as datetime = GETDATE();
| declare @now as datetime;
set @now = GETDATE(); |
Step 5: Run the upgrade script manually
In the instance of SQL Server Management Studio that you started in step 3, open and then execute the upgrade script that you changed in step 4.
Step 6: Configure the Reporting Services service to use the newly created database.
- Start Reporting Services Configuration Manager, and then connect to the instance of Reporting Services that you want to configure.
- Go to Database, and then click Change Database.
- Select Choose an existing report server database.
- Select the database that you created and upgraded in steps 1 through 5.
- Finish the wizard.