November 1, 2017 at 10:32 am
I deployed several reports to a new installation of SSRS and all reports execute a stored procedure.
All the reports work fine except for one. It's not executing the stored procedure. There's no 'No Data' message, just nothing. It works fine in Visual Studio connected to the same database and executing the same stored procedure.
To confirm, I have the stored procedures writing the parameters to a table, and this one report isn't writing anything to the table at all. I also have error written to the table in the BEGIN CATCH...END CATCH section, and nothing is there.
I also looked in ReportServer.dbo.ExecutionLog and there's nothing to suggest it even ran.
The stored procedure it runs has the same permissions as the other reports that are running fine.
All the reports have cascading parameters and these work fine on all reports except this one.
The only thing that works is the drop down menu, which are query-driven. So those are working, so it's like it sort of connects with the database.
I cannot figure out why one report doesn't run the stored procedure. Any ideas?
November 1, 2017 at 10:48 am
The report is also deployed to a dev server where it works fine - it is firing the stored procedure on the dev server as well.
Is there some setting on the test server that would keep it from running a stored procedure?
November 1, 2017 at 10:54 am
den.j.stevens - Wednesday, November 1, 2017 10:32 AMI deployed several reports to a new installation of SSRS and all reports execute a stored procedure.
All the reports work fine except for one. It's not executing the stored procedure. There's no 'No Data' message, just nothing. It works fine in Visual Studio connected to the same database and executing the same stored procedure.
To confirm, I have the stored procedures writing the parameters to a table, and this one report isn't writing anything to the table at all. I also have error written to the table in the BEGIN CATCH...END CATCH section, and nothing is there.
I also looked in ReportServer.dbo.ExecutionLog and there's nothing to suggest it even ran.
The stored procedure it runs has the same permissions as the other reports that are running fine.
All the reports have cascading parameters and these work fine on all reports except this one.
The only thing that works is the drop down menu, which are query-driven. So those are working, so it's like it sort of connects with the database.
I cannot figure out why one report doesn't run the stored procedure. Any ideas?
The execution log isn't likely to tell you anything in this case. You probably want to start by checking the Reporting Services log file. Check for the log file on the server in:
Program Files\Microsoft SQL Server\MSRS<YourVersionAndInstance>\Reporting Services\LogFiles
Sue
November 1, 2017 at 11:16 am
It might also be worth seeing what the SQL that the report is actually running is. Use Extended Events to capture the SQL and run it yourself and see if you get the same result (no data), or maybe you'll notice it's running SQL that you weren't expecting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 11:29 am
Got the log files - I see this in several places. I don't know what it means, but it seems to correspond with every time I tried to run the report.
ui!ReportManager_0-5!2764!11/01/2017-09:33:37:: e ERROR: HTTP status code --> 500
-------Details--------
System.Web.HttpException: The URL-encoded form data is not valid. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object.
at System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded()
at System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding)
at System.Web.HttpRequest.FillInFormCollection()
--- End of inner exception stack trace ---
at System.Web.HttpRequest.FillInFormCollection()
at System.Web.HttpRequest.get_Form()
at System.Web.HttpRequest.get_HasForm()
at System.Web.UI.Page.GetCollectionBasedOnMethod(Boolean dontReturnNull)
at System.Web.UI.Page.DeterminePostBackMode()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
November 1, 2017 at 11:37 am
A-ha. I googled ThrowIfMaxHttpCollectionKeysExceeded() and saw what some others have found. I have parameters which - if the user 'selects all' - results in lots of parameters. When I re-ran by deselecting the 'select all' the report runs fine.
November 1, 2017 at 12:47 pm
den.j.stevens - Wednesday, November 1, 2017 11:37 AMA-ha. I googled ThrowIfMaxHttpCollectionKeysExceeded() and saw what some others have found. I have parameters which - if the user 'selects all' - results in lots of parameters. When I re-ran by deselecting the 'select all' the report runs fine.
But that doesn't really fix the problem, as other users could still select 'select all'. Sounds like you haven't set up your SP to accept multi-value parameters, or the report shouldn't be set up to permit them
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 1:09 pm
No, it doesn't fix the problem, but at least I know what's causing the problem!
Yes, my sp does accept multi-value parameters. The SP isn't getting run.
I found some work arounds by googling the 'ThrowIfMaxHttpCollectionKeysExceeded()' message. I am looking into which will work best for this situation.
November 1, 2017 at 1:33 pm
den.j.stevens - Wednesday, November 1, 2017 1:09 PMNo, it doesn't fix the problem, but at least I know what's causing the problem!
Yes, my sp does accept multi-value parameters. The SP isn't getting run.I found some work arounds by googling the 'ThrowIfMaxHttpCollectionKeysExceeded()' message. I am looking into which will work best for this situation.
Most of them I saw were fixed after adding the MaxHttpCollectionKeys values (greater than the default 1000) to the SSRS web.config file.
Not sure if you ran across that one yet but I think it's worth a try.
Sue
November 1, 2017 at 1:43 pm
November 4, 2017 at 8:35 am
Thanks, adding the MaxHttpCollectionKeys key to web.config fixed the issue.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply