SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Server Reporting services error "Operation is not valid due to the current state of the object"


Sql Server Reporting services error "Operation is not valid due to the current state of the object"

Author
Message
nari
nari
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
Hi,
Am getting error like "Operation is not valid due to the current state of the object" when i pass the parameters from reportview control.
Description: I am passing the report columns and query parameters as parameters form WPF winform to display those columns in SSRS report.If i reselect the parameters from WPF Form and on click of view report button am getting this error "Operation is not valid due to the current state of the object " Plz help me to solve this problem.
ninu
ninu
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 67
Hi,

Were you able to solve this issue. I am facing the same problem with some of my production reports.

Thanks,
ninu
Sandy Westman
Sandy Westman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 171
I was also having this issue. I found a good post that told how to add a key to the web.config file in the folder C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager.

<add key="aspnet:MaxHttpCollectionKeys" value="1500" />

http://social.msdn.microsoft.com/Forums/ar-SA/sqlreportingservices/thread/c9d4431a-0d71-4782-8f38-4fb4d5b7a829
martin.yardley
martin.yardley
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
Hi,

I have found that Security Patch KB2656355 caused my issues.
ninu
ninu
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 67
Hi,

Thanks All.

I found out that microsoft patch KB2656352 caused the issue
kevin.booth
kevin.booth
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
Thanks, Sandy et al. Same problem here with the patches and adding the key resolved it.
Jon Russell
Jon Russell
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 1177
Thanks Sandy. Adding the key worked for me.
cphite
cphite
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 705
Adding the key worked beautifully for me; didn't even have to restart SSRS.
noah.a.helenihi
noah.a.helenihi
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 4
we were running in to this error quite a bit for an increasing number of reports with large datasets for cascading parameters. We had some reports with multiple parameters with over 1500 values in the drop-down. The solutions described above worked for some but did not work for others. That's when a colleague came up with a great fix (I am leaving all of field names as they are instead of swapping out for a generic description. You should still be able to get the point):

Fix for the subscriptions issue with POS_08:
The report POS_08 has a parameter with label Organization Unit: which has drop down values close to 1500. This report also has several other parameters with considerable count of drop down values for each parameter, which created as issue in setting up subscriptions on the report. With a high amount of parameter drop down values, even after making some architectural changes to the report server the issues was not solved.
I came up with a kind of parameter masking in the datasets. Normally to pass Organization Units to the main dataset query we use the following filter in the main query in the where clause
“dbo.vw_orgunit.orgunit in @orgunit”.
(@OrgUnit is the parameter which passes Organization Units to the query.)

Instead of using the above filter for Organization Unit, I used a modified one as 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 parameter Organization Unit is also modified as follows to add a value “All Org Units” 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]
;


This way when users want to select all Organization Units, they can just select “All Org Units”, instead of selecting and passing the whole drop down values to the query.
This reduces the execution time of the report and increases the performance of the query. After all the changes were implemented the subscriptions were also set up successfully.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search