Can't generate report when there are lot of data.

Hi there,
I have a very simple report which includes only one table to reflect
"event" table in database. It works great when there are few data like
1000 rows, but when there are lot of data like 500000 rows, the report
will run about 15 minutes and give me an error "Execution
'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
at the same time one dialogue will show up to let me login to connect
to my machine. Why is that? I google the group, got no answer.
I am sure I didn't delete anything in ReportServer and
ReportServerTempDB, is there any setting I should change?
Thanks in advance.
HenryHi,
Just make sure you have created datasource and that you have given the
userid and password. if you have given, revisit the datasource and save again.
Try using pagination, ie may be you can display about 50 records in a page
so that the fetching of remaining paged records happen in the background.
Amarnath
"fanh@.tycoelectronics.com" wrote:
> Hi there,
> I have a very simple report which includes only one table to reflect
> "event" table in database. It works great when there are few data like
> 1000 rows, but when there are lot of data like 500000 rows, the report
> will run about 15 minutes and give me an error "Execution
> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> at the same time one dialogue will show up to let me login to connect
> to my machine. Why is that? I google the group, got no answer.
> I am sure I didn't delete anything in ReportServer and
> ReportServerTempDB, is there any setting I should change?
> Thanks in advance.
> Henry
>|||Querying 500,000 records is nothing (I query 185 million row tables).
Returning that many can be problematic. I assume you are not looking at that
many records. You should make sure to use query parameters and bring back
only the data you need. Use query parameters instead of filters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> Hi,
> Just make sure you have created datasource and that you have given the
> userid and password. if you have given, revisit the datasource and save
> again.
> Try using pagination, ie may be you can display about 50 records in a page
> so that the fetching of remaining paged records happen in the background.
> Amarnath
>
> "fanh@.tycoelectronics.com" wrote:
>> Hi there,
>> I have a very simple report which includes only one table to reflect
>> "event" table in database. It works great when there are few data like
>> 1000 rows, but when there are lot of data like 500000 rows, the report
>> will run about 15 minutes and give me an error "Execution
>> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
>> at the same time one dialogue will show up to let me login to connect
>> to my machine. Why is that? I google the group, got no answer.
>> I am sure I didn't delete anything in ReportServer and
>> ReportServerTempDB, is there any setting I should change?
>> Thanks in advance.
>> Henry
>>|||I don't use any filter. It is only a simple report with a simple table.
For our system, it is very easy to have million events within a short
time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
limitation is about 230K rows of data, more than that will get the
error. How can I let customer Stop if they want to check more data? Can
I write some function to check rows of data before generating the
report?
Thanks a lot.
Henry
On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Querying 500,000 records is nothing (I query 185 million row tables).
> Returning that many can be problematic. I assume you are not looking at that
> many records. You should make sure to use query parameters and bring back
> only the data you need. Use query parameters instead of filters.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
>
> > Hi,
> > Just make sure you have created datasource and that you have given the
> > userid and password. if you have given, revisit the datasource and save
> > again.
> > Try using pagination, ie may be you can display about 50 records in a page
> > so that the fetching of remaining paged records happen in the background.
> > Amarnath
> > "f...@.tycoelectronics.com" wrote:
> >> Hi there,
> >> I have a very simple report which includes only one table to reflect
> >> "event" table in database. It works great when there are few data like
> >> 1000 rows, but when there are lot of data like 500000 rows, the report
> >> will run about 15 minutes and give me an error "Execution
> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> >> at the same time one dialogue will show up to let me login to connect
> >> to my machine. Why is that? I google the group, got no answer.
> >> I am sure I didn't delete anything in ReportServer and
> >> ReportServerTempDB, is there any setting I should change?
> >> Thanks in advance.
> >> Henry- Hide quoted text -- Show quoted text -|||A human does not look at a million events. You should have query parameters
that allows the user to get to the timeframe or the even they are interested
in. Even if they are bringing it into another program like Excel, Excel has
a limit of 65,000 rows per sheet.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<fanh@.tycoelectronics.com> wrote in message
news:1162220059.488944.185660@.i42g2000cwa.googlegroups.com...
>I don't use any filter. It is only a simple report with a simple table.
> For our system, it is very easy to have million events within a short
> time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> limitation is about 230K rows of data, more than that will get the
> error. How can I let customer Stop if they want to check more data? Can
> I write some function to check rows of data before generating the
> report?
> Thanks a lot.
> Henry
> On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Querying 500,000 records is nothing (I query 185 million row tables).
>> Returning that many can be problematic. I assume you are not looking at
>> that
>> many records. You should make sure to use query parameters and bring back
>> only the data you need. Use query parameters instead of filters.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in
>> messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
>>
>> > Hi,
>> > Just make sure you have created datasource and that you have given the
>> > userid and password. if you have given, revisit the datasource and save
>> > again.
>> > Try using pagination, ie may be you can display about 50 records in a
>> > page
>> > so that the fetching of remaining paged records happen in the
>> > background.
>> > Amarnath
>> > "f...@.tycoelectronics.com" wrote:
>> >> Hi there,
>> >> I have a very simple report which includes only one table to reflect
>> >> "event" table in database. It works great when there are few data like
>> >> 1000 rows, but when there are lot of data like 500000 rows, the report
>> >> will run about 15 minutes and give me an error "Execution
>> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
>> >> at the same time one dialogue will show up to let me login to connect
>> >> to my machine. Why is that? I google the group, got no answer.
>> >> I am sure I didn't delete anything in ReportServer and
>> >> ReportServerTempDB, is there any setting I should change?
>> >> Thanks in advance.
>> >> Henry- Hide quoted text -- Show quoted text -
>|||Henry,
what do you think you are going to do with 500,000 rows in a
report...?
I once had a customer (1993) who said the product we were selling was
no good because she could not print a list of all the customers her
company had...it was a bank...and they had 3,000,000
customers.....she could not get to grips with the idea that there is
actually nothing useful you can do with a printout of 3,000,000
customers...
Do do something useful with a report, like make a decision that makes
your company money, it generally needs to be more focused than 'all the
transactions we have had recently'...
Best Regards
Peter
fanh@.tycoelectronics.com wrote:
> I don't use any filter. It is only a simple report with a simple table.
> For our system, it is very easy to have million events within a short
> time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> limitation is about 230K rows of data, more than that will get the
> error. How can I let customer Stop if they want to check more data? Can
> I write some function to check rows of data before generating the
> report?
> Thanks a lot.
> Henry
> On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
> > Querying 500,000 records is nothing (I query 185 million row tables).
> > Returning that many can be problematic. I assume you are not looking at that
> > many records. You should make sure to use query parameters and bring back
> > only the data you need. Use query parameters instead of filters.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> >
> >
> >
> > > Hi,
> > > Just make sure you have created datasource and that you have given the
> > > userid and password. if you have given, revisit the datasource and save
> > > again.
> >
> > > Try using pagination, ie may be you can display about 50 records in a page
> > > so that the fetching of remaining paged records happen in the background.
> >
> > > Amarnath
> >
> > > "f...@.tycoelectronics.com" wrote:
> >
> > >> Hi there,
> >
> > >> I have a very simple report which includes only one table to reflect
> > >> "event" table in database. It works great when there are few data like
> > >> 1000 rows, but when there are lot of data like 500000 rows, the report
> > >> will run about 15 minutes and give me an error "Execution
> > >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> > >> at the same time one dialogue will show up to let me login to connect
> > >> to my machine. Why is that? I google the group, got no answer.
> > >> I am sure I didn't delete anything in ReportServer and
> > >> ReportServerTempDB, is there any setting I should change?
> > >> Thanks in advance.
> >
> > >> Henry- Hide quoted text -- Show quoted text -|||I agree with you, I already use a lot of parameters to limit the
report, otherwise it will be huge.
Now the question is how I can limit customers to generate a big report?
Can I write some function to count the data rows first? If more than a
specific number, then show customer a dialog? I don't know I can do
this in RS or not.
Thanks.
Henry
On Oct 30, 12:36 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> A human does not look at a million events. You should have query parameters
> that allows the user to get to the timeframe or the even they are interested
> in. Even if they are bringing it into another program like Excel, Excel has
> a limit of 65,000 rows per sheet.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <f...@.tycoelectronics.com> wrote in messagenews:1162220059.488944.185660@.i42g2000cwa.googlegroups.com...
>
> >I don't use any filter. It is only a simple report with a simple table.
> > For our system, it is very easy to have million events within a short
> > time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> > limitation is about 230K rows of data, more than that will get the
> > error. How can I let customer Stop if they want to check more data? Can
> > I write some function to check rows of data before generating the
> > report?
> > Thanks a lot.
> > Henry
> > On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> Querying 500,000 records is nothing (I query 185 million row tables).
> >> Returning that many can be problematic. I assume you are not looking at
> >> that
> >> many records. You should make sure to use query parameters and bring back
> >> only the data you need. Use query parameters instead of filters.
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in
> >> messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> >> > Hi,
> >> > Just make sure you have created datasource and that you have given the
> >> > userid and password. if you have given, revisit the datasource and save
> >> > again.
> >> > Try using pagination, ie may be you can display about 50 records in a
> >> > page
> >> > so that the fetching of remaining paged records happen in the
> >> > background.
> >> > Amarnath
> >> > "f...@.tycoelectronics.com" wrote:
> >> >> Hi there,
> >> >> I have a very simple report which includes only one table to reflect
> >> >> "event" table in database. It works great when there are few data like
> >> >> 1000 rows, but when there are lot of data like 500000 rows, the report
> >> >> will run about 15 minutes and give me an error "Execution
> >> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> >> >> at the same time one dialogue will show up to let me login to connect
> >> >> to my machine. Why is that? I google the group, got no answer.
> >> >> I am sure I didn't delete anything in ReportServer and
> >> >> ReportServerTempDB, is there any setting I should change?
> >> >> Thanks in advance.
> >> >> Henry- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -