SSRS exceptions The operation is not valid due to the current state of the object. and AspNetSessionExpiredException

Question

In my asp.net application, I have a report that connects to the ssrs endpoint when running reports with a large number of parameters (select all options in the parameter list) I get the following exception:

Operation is not valid due to the current state of the object. 

I saw several blogs as well as posts mentioning the security patch used by MS as a reason for this, which limits the number of items you can have in the collection to 1000.

I also saw suggestions to add the appSettings tag in the web.config file for the wcf hosting service located in C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer and add the following entry

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

I tried these suggestions with no luck. The server running sql2008r2 does not have any of the security updates mentioned, and adding the appSettings value to web.config (config for the ssrs management site or endpoint) has no effect on the exception. I even increased the value (100000) in case I had more than 20k items.

My second thought was that the packet size in the wsHttp request could be large, and I also need to increase it.

Update After I unearthed a few more, I found myself on the right track. The above was a partial solution according to the record MS KB2661403 MaxCollectionKey was only part of the record. The second part is the serialization of parameters, the second entry should be added to the appSettings section

 <add key="aspnet:MaxJsonDeserializerMembers" value="1000" /> 

As I began to think about it, I was also mistaken when it was necessary to introduce. It does not need to be entered at the SSRS web.config endpoints, but on the site that hosts the report viewer control. Adding these two entries to the web.config file led to the successful transfer of objects. Now the SSRS system throws an ASP.NETSessionTimeOut exception, so I need to try increasing this timeout to see if I can return data to large requests.

Does anyone have any suggestions or recommendations?

-Cheers

Answer 1

You cannot always reverse engineer a report - the client wants it when the client wants it.

However, we often encountered this error to increase the number of reports with large data sets for cascading parameters. In the drop-down list there were several reports with several parameters with more than 1500 values. The solutions described above worked for some, but did not work for others. This is when a colleague (Pranay Papishetty) came up with a great solution (I leave all the field names as they are instead of replacing them for a general description. You can still understand):

Fix subscription issue with POS_08:

The POS_08 report has a parameter labeled Organization Unit: whose drop-down values ​​are close to 1500. This report also has several other parameters with a significant number of drop-down values ​​for each parameter, which are created as a problem when setting up a subscription to the Report. With a large number of parameters for outliers, even after some architectural changes were made to the report server, the problems were not resolved.

I came up with masking parameters in datasets. Usually we use the following filter in the main query in the where clause to transfer the units of the organization to the main dataset query

 dbo.vw_orgunit.orgunit in @orgunit 

(@OrgUnit is a parameter that passes organizational units to the request.)

Instead of using the above filter for Unit Unit, I used a modified one as shown below:

 ((dbo.vw_orgunit.Orgunit IN ((SELECT item FROM [DW_Master].[dbo].fn_Split (@orgunit_d, ',')))) OR (@orgunit_d) IN ('All Org Units')) 

The dataset corresponding to the Organization Unit parameter is also changed as follows to add "All Org" values ​​to the drop-down values.

  SELECT DISTINCT [OrgUnit] AS [OrgUnit_Code], [OrgUnitDescription] AS [OrgUnit_Desc], ([OrgUnit] + ' - ' + [OrgUnitDescription]) AS [OrgUnit_Label] FROM dbo.vw_DimOrgUnit WHERE ( [OrgLevelAgency] = '73' AND [OrgLevelFiscalYear] IN ( @FiscalYear ) AND ([OrgLevel_1] + ' - ' + [OrgLevel_1_Description]) IN ( @OrgLevel_1_Label ) AND ([OrgLevel_2] + ' - ' + [OrgLevel_2_Description]) IN ( @OrgLevel_2_Label ) AND ([OrgLevel_3] + ' - ' + [OrgLevel_3_Description]) IN ( @OrgLevel_3_Label ) ) OR [OrgUnitSKey] = '-1' OR ( [OrgUnit] = '5117' AND [OrgUnitFiscalYear] = '2013' /* A Special Case*/ ) UNION SELECT '' , '', '(Blank)' UNION SELECT 'none' , 'none', 'None' UNION SELECT '-100','All Org Units','All Org Units' ORDER BY [OrgUnit] ; 

Thus, when users want to select all units of the organization, they can simply select "All control units" instead of selecting and passing all the outliers to the request. This reduces report execution time and improves query performance. After all the changes were completed, the subscriptions were also successfully configured.