String parameter truncated in URL for report
I've added a parameter to one of my reports that filters the returned values based on a comma-separated list of values entered into a parameter of the "string" datatype. The report runs without errors, even when submitting a large number of values (typically
five characters each). The cooresponding parameter in the underlying stored procedure is a VARCHAR(2000), and so far my strings should not be passing more than about 800 characters.
When I run the procedure in SSMS, it runs successfully and returns all expected rows.
When I preview the report from Visual Studio, it runs successfully and returns all expected rows.
When I run the report from our reporting services portal, it returns only a subset of the rows. I viewed the URL and observed that the string for the parameter is passed in the url but was truncated. The shortened URL is 588 characters.
If I manually update the URL to include all of the values from the original parameter, the report runs successfully and returns all expected rows. The length of the full URL is 784 characters.
The full length of the parameter string is 373 characters. The number of characters from the string that are apparently being used to generate the URL is 257 characters.
Can anyone help me understand why the parameter is shortened when the URL is generated?
Thank you in advance.
July 16th, 2012 1:36pm
Hi There
Thanks for your Posting. Which Version of visual studio you are on ? If you report run for some multivalued parameter but when you select all it does not work,
I think you can change the value MaxHttpCollectionKeys, the default value of MaxHttpCollectionKeys is set to 1000 i think. You can override the value by adding the following key to the appsetttings
section in your SSRS report manager web.config file (usually in Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager)
<add key="aspnet:MaxHttpCollectionKeys" value="20000" />
<add key="aspnet:MaxHttpCollectionKeys" value="20000" />
20000 is just a quick suggestion for you, you can set it to the value of you would like.
for more details please see this thread
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cb6ede72-6ed1-4379-9d3c-847c11b75b32
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/89ca7b3d-80f1-4a4f-b8c7-df1f377132e3/
If you have any question please let me know.
Many Thanks
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 4:55pm
Thanks for the reply!
We're using Visual Studio 2005 in this case, but the parameter in question is not a Multi-Value parameter, but a string. We allow the user to free-text enter a comma-separated list in the parameter field. Is your suggestion still applicable?
Matt
July 17th, 2012 1:23am
Hi There
Thanks for your posting. Can you please have a look on this thread that might help you
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/f41d2e73-4799-4421-bf7d-8a505a3055aa
Many thanks
Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2012 1:43am
Thank you, that link indicates that "Internet Explorer has a maximum URL length of 2,083 characters" which tells me that the problem is not
the length of the generated URL, at least in terms of what the browser can handle.
I reviewed the option to generate the report by gathering parameters with a form and then using POST, but that option will not be applicable in our environment.
Anyone have other ideas why the URL might be getting truncated?
Matt
July 17th, 2012 9:51am
Hi There
Thanks for your posting again. Please have a look on this that might be helpful
http://www.bidn.com/blogs/MikeMollenhour/ssis/248/reporting-services-2008-error-maximum-request-length-exceeded
http://go4answers.webhost4life.com/Example/maximum-request-length-exceeded-help-45209.aspx
It talks about the change needs to made to the report manager web.config. Please see details it might fix your problem
Many thanks
Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 12:26am
If I was exceeding the maxRequestLength, wouldn't I be seeing an error (as in the first link)?
Just in case, and per the second link, I updated the web.config file in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager thusly:
<!-- MMR 07/18/2012 - BEGIN
<httpRuntime executionTimeout="9000"/> -->
<httpRuntime executionTimeout="9000" maxRequestLength="10240"/>
<!-- MMR 07/18/2012 - END -->
I then tried running the report again with no change in the result (URL still truncated). I then recycled the app pool containing the Reports application and tried running the report again; no change.
Is there anything else I need to do to make this change take effect?
Thank you for your continued assistance.
Matt
July 18th, 2012 2:02pm
Hi Matt
Thanks for your posting again. Did you try to have a look inside your report server log file if you find anything (Error message etc.) after running the report? Please also try to restart reporting services Service and see if it makes any difference
Many Thanks
Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 4:52pm
I checked the ReportServer log file (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles) and saw entries corresponding to when I ran the report, but no errors.
I'll restart ReportingServices after hours tonight and let you know if it makes a difference.
Matt
July 18th, 2012 5:03pm
Restarted ReportingServices and tried my report again. No luck.
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 8:02pm
Hi There
Thanks for your posting. One more try
1. Go to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
2. Open machine.config
3. Search for <system.web>
4. Add in <httpRuntime executionTimeout="9000" maxRequestLength="102400" /> under the <system.web>
5. Save
6. Restart IIS
I hope this might resolve your problem
Many thanks
Syed
July 18th, 2012 8:23pm
No luck.
In the file C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config, I added this line right below <system.web>:
<httpRuntime executionTimeout="9000" maxRequestLength="102400"/>
I then restarted IIS, launched my browser and ran the report from our portal with the same results: truncated URL.
Matt
Free Windows Admin Tool Kit Click here and download it now
July 19th, 2012 12:33am
How is the @param that's being truncated submitted to the report?
Do you have a custom web front end that you use to call the report and pass in the @parameter as part of the URL?
Or are you running it in Report Manager and free texting the list of values and hitting view report?
What happens when you add a textbox t othe report and have it display the contents of the @parameter. Is it truncated when viewed via your portal or shown completely?
July 19th, 2012 1:21am
Great suggestions, Jakubk.
I'm running the report from a front-end portal application that has a field for entering the parameter.
I added a textbox to the report that displays the parameter value. When I run it from our portal, the textbox shows the truncated parameter string.
When I run the report from report manager, the textbox shows the full string.
I'll escalate internally to our developers to determine why our form truncates the string before passing it to the report.
Thank you Syed and Jakubk!
Matt
Free Windows Admin Tool Kit Click here and download it now
July 19th, 2012 12:50pm