SharePoint (and Project Server) Shenanigans

You may encounter an exception when running certain SQL Server Reporting Services (SSRS) reports.

The exception message will generally say something along the lines of Operation is not valid due to the current state of the object.

You may encounter this either in reports with a large number of parameters, or in reports where the parameter counts are low but the parameters themselves are multiple choice and have a large number of items selected.

The cause of this is a security fix released by Microsoft under Security Bulletin MS11-100. The fix, among other things, limits the number of keys that ASP.NET applications can parse as part of a request; large numbers of keys could potentially allow a denial of service to occur.

Unfortunately the effect of this is that our large parameter set is blocked, causing the exception and presenting a nasty error to our end users.

Luckily there is a solution to this.

The trick is to add a section to your web.config inside the <appSettings /> section.

<appSettings>
	...
	<add key="aspnet:MaxHttpCollectionKeys" value="5000" />
	...
</appSettings>

The actual value used should reflect the number of keys you believe will be used, but 5000 is a reasonable starting point.

If you are using SSRS in native mode, the web.config file is located within the reporting services installation path, usually C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer.

If you are using SSRS in SharePoint integration mode, the modification needs to be made to the web.config of the application pool where you are running your reports. For this I would recommend that you create a solution that utilises the SPWebConfigModification class as part of a feature receiver, rather than making the changes directly. This will ensure that the change is made across your SharePoint farm simultaneously without the human error that could come from doing the change manually on multiple servers.

Note: In both cases, these modifications either require a restart of the reporting server or in the case of SharePoint a recycle of the Application Pool. This will result in short outages so you will want to schedule this as part of maintenance accordingly.

For more information I would recommend reading the relevant KB article.

via Chris on SharePoint http://spchris.com/2013/05/sql-server-reporting-services-reports-errors-when-large-numbers-of-parameters-are-selected-2/

SharePoint and Project Server Consultant MCITP – SharePoint Administrator 2010 MCTS – Microsoft Project 2010 – Managing Projects, Project Server 2010, Configuration, SharePoint 2010, Configuration Prince 2 – Practitioner This article has been cross posted from spchris.com (original article)