Answered by:

Migration from SSRS 2005 to SSRS 2012

Question

I have just done an upgrade of our SSRS environment in development from SQL 2005 to SQL 2012 (SP1).

The process I followed was that documented by Microsoft, basically backup and restore database and encryption key, however we have an issue in that certain reports are not loading.

Reports created using a data source work without any issues however reports using specific reporting models are not working. The error log is very vague however the stack dump produced does provide this message -

Report Models

Semantic modeling language (SMDL) report models are deprecated. Although you can you continue to use existing report models as data sources in SQL Server 2012 Reporting Services reports you should consider updating your reports to remove their dependency
on report models.

The bit I find interesting is in the stack dump where at the end it says "Unsupported object type: System.Uri" - I wondered if this could be something in the report model that was using an assembly or something that wasn't moved to the new machine.
Although I've no idea how to confirm that and determine the missing assembly.

2) Updated to SQL 2005 SP2 and restored the report server databases from live - all reports are now visible in SSRS and load correctly - this is as live is.

3) Updated to SQL 2005 SP4 to ensure that it works at this point - everything works correctly.

4) Run the upgrade advisor, it complained about a HTML3.2 extension being used so I've removed this from the config, this warning is then removed. The only other warning was "SQL Server 2005 Report Server Web Service group detected".
I don't believe this is an issue so I've proceeded.

5) Backup the databases and key

6) Install SQL 2012 SP1 and set up the SSRS - all works.

7) Restore the databases and key - the reports appear everything works EXCEPT the reports related to one specific report model which throws out the same error as above.

Logins on the machines match up, everything seems fine. With regards to processors, the live servers are Intel Xeon and the development machine for this test has Intel i7's.

I’ve gone over everything I can think of from the SSRS side and I’m fairly confident that it’s ok so to me that would mean it would have to be something in the model (also the fact that other models work) but I don’t know how to determine what exactly it
is that is the problem in the model.

In SQL Server Reporting Services (SSRS), because it have back compatibility, we can deploy SSRS 2012 reports to SSRS 2005 Report Server. However, there may be some erratic behaviors when we deploy a SSRS 2005 report to SSRS 2012 server.

I recommend you try to open those projects in SSDT, it will upgrade to SSRS 2012 instance automatically. Then redeploy it to report server.

Opening the project in SSDT does not work, whilst it upgrades the project it is not possible to open the report model definition as you receive an error saying;

"...can not be opened because it's project type (.smdlproj) is not supported by this version of the application".

Also I'm a little confused on the compatibility, I would have expected it to be that I can open SSRS 2005 reports in SSRS 2012 as they are upgraded when they are opened. If this is not the case then it would mean that every upgrade from 2005 to 2012 would
be require every report to be re-created in SSRS 2012.

I wouldn't have expected to be able to open SSRS 2012 reports in SSRS 2005 and this isn't what we would be doing in this case. All we are doing is upgrading from SSRS 2005 to SSRS 2012, and it's only this one report model which has the issue.

If we are used SQL Server 2000 or SQL Server 2005 running in 8.0 compatibility mode, we must upgrade the Database Engine or choose another Database Engine instance. More detail information, please refer to the articel below:

The ReportServer and ReportServer tempdb databases were in 9.0 (2005) mode on the original server. These were then backed up and transferred to the SQL 2012 instance and restored there were they remain in 9.0 mode although I have tried changing the compatibility
mode to 10 (2008) and 11 (2011).

The upgrade advisor gave only 1 warning, relating to moving the ACL's although I don't believe this to be a problem. As I said this is only affecting one report model, it is not affecting other reports or report models and is therefore not widespread.

Is there anyway for us to determine the exact part of the report model which is causing the issue, the logs state - 'unsupported object' so something in there must not be supported anymore, is that correct?

There are also then roughly 30 warnings, a sample of one of these is below;

"The Relation property of the Role "BPR / Claims Reference" refers to the Target end of the Relation "<View name>", which is not bound to a set of uniquely constrained columns for the Table "<Another view name>". Roles with Cardinality of One
or OptionalOne require relations bound to uniquely constrained columns of the table. Reporting Model 2.smdl 0 0 "

I don't believe these warnings are stopping the deployment but I'm not so sure on the messages? Could these be causing the issue?

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.