You cannot use SQL Server 2005 to host ReportServer 2012 databases

Article translations Article translations
Article ID: 2796721 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

After you create a Microsoft SQL Service Reporting Services (SSRS) 2012 database against a Microsoft SQL Server 2005 database engine from SQL Server Configuration Manager, you receive the following error message when you browse the ReportServer or ReportManager databases:
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '143'. The expected version is '162'. (rsInvalidReportServerDatabase)

Cause

This issue occurs because of a problem with the script that is generated to upgrade the newly created database to the correct schema version. Specifically, the upgrade script contains in-line value assignments that are not supported for SQL Server 2005. (However, the statements are supported for SQL Server 2008 and later versions.)

Resolution

To resolve this issue, you can manually generate the database creation scripts and upgrade scripts and then change them to remove the incorrect statements before you execute them to create and upgrade the ReportServer databases.

The in-line value assignments in the upgrade scripts should be broken down into two statements so that the declaration and the assignment are separate.

More information

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

  1. Start SQL Server Management Studio, and then connect to the SQL Server 2005 Database Engine that will host your ReportServer database.
  2. Open the script that you created in step 1.
  3. 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.

Collapse this tableExpand this table
Original statementNew 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.

  1. Start Reporting Services Configuration Manager, and then connect to the instance of Reporting Services that you want to configure.
  2. Go to Database, and then click Change Database
  3. Select Choose an existing report server database
  4. Select the database that you created and upgraded in steps 1 through 5. 
  5. Finish the wizard.

Properties

Article ID: 2796721 - Last Review: December 31, 2012 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Standard
Keywords: 
KB2796721

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com