2083 Character Limit Problem with IE URL Report Request

Question

Post reply

2083 Character Limit Problem with IE URL Report Request

thejim

SSC Enthusiast

Points: 147
More actions
February 23, 2005 at 11:02 am

#113457

I've run into a problem in sending too many parameters in a URL report request.
Before anyone gives me too hard a time: this URL request is being sent from the form via *POST*. In theory the querystring length shouldn't be the problem. But it is.
Also, I know the report works because the same number of parameters works if manually typed into the report manager view.
The switch from post to get (and the associated querystring length issue) is done by javascript at the report server. It looks like it takes the posted (header) form data and the javascript switches the call to a querystring.
Apparent Order of Events:
-Post submits the parameters in the header of the request (not the querystring).
-The report server accepts the parameters and builds a javascript page for it to display the report.
-The conversion to a javascript page converts the given post data to a querystring. The conversion to javascript throws extra wrenches into this because it encodes all non alphanumeric chars into html speak (a space = "%20") which makes each space, comma, etc take *3* spaces of our precious 2083 chars.
-As soon as too many chars are sent, the "report is being generated" spinning wheel just spins and spins. For days if you allow it.
I've searched and not found any other folks admitting this same issue - though MSDN online has the following to say: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_35pi.asp - read the "note:" about half way down the page.
Work arounds I've come up with: Instead of delimiting the submitted list of UserID's to report on with a comma (which becomes "%2c" in javascript / html encoding) I can replace the commas with "C" right before the form post happens on the filter page. Then in the stored proc that builds this report, I can replace the "C" with commas. Yikes. Talk about ugly, but it gains 2 spaces per comma. This almost solves my problem, however, in my situation, it's still possible for the user to choose more userid's than even this will allow in the 2083 character limit.
Other idea I've had to dismiss based on not working - hop over the javascript and paste the url directly to the browser - the report delivers the first page, but there are no buttons at the top to go to page 2, or to export.
What does work: is if the customer uses Netscape or Opera to view the reports. These browsers also have a querystring length issue, but it's up near 30,000 characters and does solve the problem (I've checked). However, it's unreasonable to expect all users to run an alternate browser.
Has anyone else dealt with this issue?
Any brilliant ideas?
I'm becoming desperate enough to consider coming up with some kind of parameter compression scheme on the front ASP filter page during the POST, and then decrypt it in the sql. PLEASE STOP ME!!
February 28, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

thejim SSC Enthusiast Points: 147 More actions · Answer 1

In case someone else runs into this problem. . .

How I ended up addressing this:

The parameters being sent were all comma seperated numbers (selected user ID's). I wrote a little "compress" stored proc and an "uncompress" stored proc to solve the issue.

Notes: I got decent performance replacing numbers in a ",10" = "A"; ",11" = "B" type arrangement, after running out of the 26 letters then going to ",27" = "A-" (the 2 characters is still shorter than the 5 characters it would've encoded to). And then the last step was to replace any remaining "," with ".". Your only available characters to use in the compression are single case letters, and the following symbols: -_*. as these don't get encoded in javascript. You can only use single case letters if you're are in a non-case-sensitive SQL instance (which is the default) - it won't be able to decern between "A" and "a" in decoding.

Also, due to weird errors like 'having problems converting varchar to int' in the variable, I had to CSTR the (already) string report parameters again before submitting them to the "uncompress" stored proc.

This wasn't fun. And it IS ugly. But it works (for now).

This isn't the most robust fix, if you had a long enough list of user ids, you'd still pass the 2083 character limit. After the fact, I think a better solution may be to save the parameters into a table keyed off of the ASP SessionID and the report name. The stored procedure that feeds the report could then parse the needed parameters from the stored info in the table and all that would need to be sent in the querystring would be the sessionID and the report filename (which you may be able to parse out since it's already being sent in the querystring - otherwise you'll have to add it as a specific parameter).

data_czar Ten Centuries Points: 1147 More actions · Answer 2

Could you write the user IDs to a table in your DB. Then change the Reporting Stored Procedure to join to that table.

Then you don't have to pass a lengthy string through in the URL

Just a thought

thejim SSC Enthusiast Points: 147 More actions · Answer 3

The only hangup was that I was allowing them to dynamically choose from a list of users in a filter ASP page before being sent to the report. If they chose enough users to report on, they passed the 2083 char limit - and the report would never generate.

If it was a static list of userID's your idea would work.

But I may be missing something. Let me know. I'm just getting started with RS.

data_czar Ten Centuries Points: 1147 More actions · Answer 4

Create a table in your database that could have as few as 2 columns (user_id, session_id). The session_id will be used to manage concurrency (i.e More than 1 user trying to launch this report at the same time)
When the report user selects the users on your report lauching page, and when they click the submit button, delete any existing user_ids FOR THIS Session_ID, and save the newly select user_ids to your new table.
Modify the stored proc or query that your report is based on to add INNER Join between the data table and this new table (ON data_table.user_id = new_table.user_id), and add AND new_table.session_id = @session_id to the WHERE clause. You'll need to pass the web session id to the proc

This join will perform the filter for you on the db. It's easier than it sounds.