Reporting Service Point (RSP) broken after upgrading SQL Server 2012 SP1 #sysctr


Last week an upgrade of the Configuration Manager 2012 SP1 SQL Servers was scheduled . This was a straightforward upgrade of SQL Server 2008 R2 SP1 to SQL Server 2012 SP1 which was affecting both Central Administration Site (CAS) server and Primary Site Server (PSS).

Issue

After the upgrade we noticed that Reports are not longer available. Opening a random report resulted in an error:

Failed to load expressions host assembly. Details: Could not load file or assembly ‘SrsResources. Culture=Neutral’ or one of its dependencies. The system cannot find the file specified.

image

Root Cause

As SQL Reporting Reporting Services is a component of SQL Server setup and thus Reporting Services will be upgraded as well. By default, the installation directory of SQL Server 2008 R2 Reporting Services, which may very to your custom parameters, is the following:

C:\Program Files\Microsoft SQL Server\MSRS10.50.MSSQLSERVER

 

Due to the upgrade from SQL Server 2008 R2 to SQL Server 2012 the default installation directory has changed to:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER

 

So the configuration defined in rssrvpolicy.config does not match the new situation. The SRSResources assembly url in rsssrvpolicy.config is still pointing to the installation path of SQL Server 2008 R2 instead of SQL Server 2012.

Solution

This issue can be solved by performing the following steps:

  • Change the SRSResources assembly url in rssrvpolicy.config which is located in the new SSRS installation folder .\MSRS11.MSSQLSERVER\Reporting Services\Report Server\Bin

image

to

image

  • Copy srsresources.dll from the old SSRS installation folder .\MSRS10.50.MSSQLSERVER\Reporting Services\Report Server\Bin to the new SSRS installation folder .\MSRS11.MSSQLSERVER\Reporting Services\Report Server\Bin.
  • That is it!

Preferred Solution

Although we fixed this issue in a production environment and were able to reproduce it in a lab environment I am not sure the above steps are a recommended approach or are even supported by Microsoft. I guess a rhetoric question ;-).

To be sure you are taking a ‘supported’ route the best way is to remove the Reporting Service Point (RSP) role and add it back again. How to install RSP is described in this TechNet wiki.

image

Hope this helps!

Advertisements

4 thoughts on “Reporting Service Point (RSP) broken after upgrading SQL Server 2012 SP1 #sysctr

  1. Pingback: ConfigMgr 2012 SSRS fails after upgrade to SQL 2012 | Steve Thompson [MVP]

  2. v-juanm

    Scenario SQL 2012 P3 upgrade to SQL 2014 SP1 upgrade

    Path for SQL 2012, rssrvpolicy.config

    C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

    Path for SQL 2014, rssrvpolicy.config

    C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer

    In the SQL 2012 to SQL 20124 upgraded in place server go to

    C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin

    and copy srsresources.dll from there to

    C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin

    Inside open your rssrvpolicy.config
    Locate

    So because you already copied srsresources.dll to your
    C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin
    location, edit old URL
    from
    Url=”C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\srsresources.dll” />
    to
    Url=”C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\srsresources.dll” />

    Restart your Reporting services

    If you have any services like in my case sccm current branch 1602, these will pointing to your SQL 2012 so after upgrade follow this process and reboot your SCCM server will bring back your SCCM Report Back

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s