September 16, 2010 at 11:00 am
Hi gurus,
I have report which display list of customer.Production db has a million customer and when you run it of course it give an error 'An error has occurred during report processing.
Exception of type 'System.OutOfMemoryException'
It is any way to solve this problem or a list give a use some more user friendly message like "There are too many records. Please refine your criteria and try again'
Thanks in advance
September 16, 2010 at 11:06 am
Well what are you doing now?? There's no way printing anything over 1000 customers is going to be of any use to anyone.
And I know SSRS easily handle that (assuming your server is not crashing or under-powered).
What's the actual requirement of the users?
September 16, 2010 at 11:17 am
my server is not crashing and i need to show list of all active cleints.
I have @group parameter,when some group choose report working fine,but for "All' it give an error
September 16, 2010 at 11:22 am
Well assuming you can't get out of the all option, get a better server (at least more RAM). Nothing else will change that error if you don't reduce the load.
You can always search optimisation technics for that report but I'm afraid that's not going to help a lot since this is a list report. Most optimisations are done in formating, grouping, sub reports. If you're not using any of those options then I'm afraid you have your hands behind your back on that one.
If that helps, other than "hard copy" backups required by a stupid law, that list is going to be completly useless to anyone. Either I'm missing something in the requirement or the solution to the problem is wrong or there's no problem and someone is just wasting your time.
September 16, 2010 at 11:29 am
too sad,
Thanks
September 16, 2010 at 11:37 am
natalie_orlovsky (9/16/2010)
too sad,Thanks
So what are the actual requirements and the reasons behind them? I can't see any circumstances where I'd pay an employee to read that list. There's just no point.
If you need to export it to excel or csv to a mailing software then you have options like bcp in/out which is lightning fast and DESIGNED for that type of work.
September 16, 2010 at 11:48 am
Add a second dataset to the report, use the exact same parameters do get a count of all customer records matching the query parms. Write this count on the report somewhere. Modify your query to use something like TOP 1000, add a textbox with an expression along the lines of if fullrecordcount >= 1000 then 'Showing first 1000 customers of ' + fullrecordcount else 'Showing all ' + fullrecordcount + ' customer records'.
So, if they use parms, they'll get the full set (up to 1000). The 1000 is arbitrary but likely achievable on nearly any hardware.
You'll be showing them the full count (as a number) of what they're trying to retrieve.
As your friendly ninja has pointed out, there is likely a problem with the solution 🙂 Every mention of 'they' means your user community -->
- If they need to print out the list, then 1000 is usually sufficient. If they need over 1000 then this isn't a report it's an extract, use a different tool.
- If they tell you they need the full (1MM rows) set but will use the 'Save As' function to save to Excel or CSV, then this isn't a report it's an extract, use a different tool.
- If you have a legal requirement to hold a written copy of all customer records, attempt to view/review the wording of the requirement yourself. *Timing* plays a large part in this - if you have 1MM customers *now* then chances are you gain/lose ~ 0.5-2% of these with some cyclical nature. Are you meant to print a new copy on the loss/gain of each customer? I'm guessing not. Let's assume you do have this legal need, then again, this isn't a report, it's an extract and likely means using a different tool. No (none, nada, zip) user involvement is required to produce a full list of customer records.
Good luck!
HTH,
Steve.
September 16, 2010 at 11:50 am
Thank you so much i'll try that
September 16, 2010 at 12:00 pm
Ya, bcp out to csv is really simple (even without prior experence). It runs like lightning (seconds per MM records). And can be used in a job and scheduled for regular, unattended executions.
But then again, this comes only as a requirement only to obey a law. My customers only care about what the users that are doing now (give or take a quarter), not what happened 10 years ago (which only the auditors are caring about).
September 16, 2010 at 12:03 pm
look it is not acceptable for me, i know everything about bcp,but reqt to retrieve all list of customer from web,from report
September 16, 2010 at 12:12 pm
I don't understand what you are saying.
If you know everythin about BCP, then it means your requirements are different from everything I ever had to do. So I would need to see them.
One option does not preclude the other. Your report seems to be working except for that ALL option. I can't see anything usefull done in the SSRS reportviewer screen with that list other than export to excel and play with it.
Assuming this is the case, you can always do the export from sql to excel daily and let the users play with that file.
Again, to solve the error you'll need to free ram for SSRS, no way around that.
Also keep in mind that unless you have excel 2007 / 2010, you won't be able to export that many rows into it.
September 16, 2010 at 12:14 pm
problem is i can not give the user play with a file,i mean i can,but user should be able pull up the report by himself and what ever he want,daly,monthly,weekly
September 16, 2010 at 12:23 pm
Do the export daily from SQL to Excel (or whatever their choice) and name the file based on dates.
Give them the path to the shared folder.
That's for the full file.
For the group filter, then can always apply in SSRS or Excel too. I'd let them filter it in Excel.
Doing it that way is also better to not crush SSRS under heavy repeated loads.
I know this is annoying, but when the specs or solutions are wrong, you need to show them the right way and sell them on it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply