| Author |
Topic |
|
AlexP
Starting Member
34 Posts |
 Posted - 2005-07-27 : 09:46:38
|
| This is critical. The company I am in runs 3 reports that calculates huge amounts of data and it takes about 45 minutes to run. Unfortunately I get the following error after about 30 minutes. I need to fix this today. No one is cancelling the report it just appears to time out.Any clue guys?Reporting Services Error-------------------------------------------------------------------------------- An error has occurred during report processing. (rsProcessingAborted) Get Online HelpQuery execution failed for data set 'DataSet1'. (rsErrorExecutingCommand) Get Online HelpOperation cancelled by user. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 10:26:30
|
| Start by running the SQL in QA there might be an error there.45 minutes... I would try to optimize that query if possible.rockmoose |
 |
|
|
jhermiz
3564 Posts |
Posted - 2005-07-27 : 11:46:18
|
What does dataset1 containt, you may want to when you create datasets names them something more descriptive rather than the defaults. For instance, if you have a dataset that pulls customer names for a parameter one might call it dsCustomers.Post the sql for dataset1.Is this the only dataset in the entire report?If the query takes 45 minutes, you will need to change the timeout property of the dataset. Are you dealing with cursors here ?Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
AlexP
Starting Member
34 Posts |
Posted - 2005-07-27 : 12:23:19
|
| thanks but actually it was simple. the site settings has a setting set to timeout at 30 minutes. |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-27 : 13:08:31
|
quote:
Originally posted by AlexP
thanks but actually it was simple. the site settings has a setting set to timeout at 30 minutes.
I still dont think a 45 minute time is acceptable, want to post your sql ? Or are you happy with that ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 13:31:56
|
It's the "After Lunch" report  rockmoose |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-27 : 13:47:04
|
Luckily for us we get an hour, some lunches are 30 minutes that would really suck!
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 14:43:22
|
| I wonder how it is like to wait for 45 minutes for a report in RS.(don't forget to keep staring at the processing animation the whole time)That must be a real zen test of patience.We have some that take up to 5 minutes, and that is bad, really bad.rockmoose |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-27 : 15:26:40
|
Yikes 5 minutes for a report ... I wouldn't want to run it. What you can do however, is cache the data, there is an option in rs for that. However, if the data is critical that is pointless. But you're pretty swell with SQL so I'm sure they are optimized :)...Could be accounting reports, those are always fairly large. I like that little whirlwind RS logo :), I think they copied off of the mozilla / firefox family with that thing. Top right corner of the firefox browser has that when you request a page on the internet.I can't complain though, RS is an elegant tool unlike a lot of reporting tools out there, the way the integration and the use of XML makes it portable.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 15:38:14
|
| They are aggregating data from a 60+ mil rows partitioned transaction view, with about 3 years data.(the physical tables are partitioned yearly, 1 year each, 25 mil rows, every year we take one out)They provide selection criteria on the timeperiod, salerep, geography or such.With a pviot twist in the end.Each query is different, so we can't cache it very well.We can't summarize the data either, because the granularity of the different reports/queries varies.However, before they had almost no reports, so they are happy for now.rockmoose |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-27 : 15:56:27
|
Lots of data...95 more and youll be at 2k..congrats ;)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 16:34:57
|
quote:
Originally posted by jhermiz
Lots of data...95 more and youll be at 2k..congrats ;)
Thanks,I've been very busy posting today.2k, gee, almost feeling old.rockmoose |
|
|
|
AlexP
Starting Member
34 Posts |
Posted - 2005-07-27 : 16:45:24
|
| We are in the process of optimizing that long query but in the meantime one the longer reports diplays this error- "Page Not Found." I extended the IIS properties for the reporting services to 3 hour for session state and asp script timeout to see if that would help.Any clues on this one? |
|
|
|
|
Reporting Services Error-URGENT!!!