Importing Error: fails

Showing posts with label fails. Show all posts

Friday, March 30, 2012

Report Does Not Render!

Hello,
I am trying to get my report to render on the Report Server, but it
fails to do so. I am able to see that the report is generating, but
after a period of 30 or more minutes, the same image is displayed on
the screen. I know, for sure, that the query that is grabbing the data
takes about 10 minutes. What could be the problem? I have increased
the timeouts in every place possible: IIS, Report Manager, the data
source. Am I missing something? Please help! Thanks!
-MichaelHow many rows of data are being returned? Are you using filters or query
parameters? How is it being rendered (the default is html)? Or are you
specifying PDF?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<mjhaddad@.gmail.com> wrote in message
news:1119397987.766833.119040@.f14g2000cwb.googlegroups.com...
> Hello,
> I am trying to get my report to render on the Report Server, but it
> fails to do so. I am able to see that the report is generating, but
> after a period of 30 or more minutes, the same image is displayed on
> the screen. I know, for sure, that the query that is grabbing the data
> takes about 10 minutes. What could be the problem? I have increased
> the timeouts in every place possible: IIS, Report Manager, the data
> source. Am I missing something? Please help! Thanks!
> -Michael
>|||Bruce,
Thanks for your reply. Originally I was returning 10,000 rows of data,
but I have limited it to 100 rows. I am using 4 query parameters: a
start date, an end date, sort by, and hotel property. It is being
rendered by the default, HTML. I also want to mention that the report
does show up when the query execution time is around 3 minutes or so.
Thank you for your help!|||Michael:
Have you received any further feedback on this? I am experiencing a similar
issue when i have a report that returns about 10,000 rows. With less rows
and a quicker query execution (6 minutes or less) the report comes back.
-Jeff
"Michael" wrote:
> Bruce,
> Thanks for your reply. Originally I was returning 10,000 rows of data,
> but I have limited it to 100 rows. I am using 4 query parameters: a
> start date, an end date, sort by, and hotel property. It is being
> rendered by the default, HTML. I also want to mention that the report
> does show up when the query execution time is around 3 minutes or so.
> Thank you for your help!
>|||RS renders in RAM. How much data it handles without major slowdown depends
on the format. 10,000 rows is not something for a human to look at. That
much data in my mind is a data export to another program (like Excel).
Rendering to HTML and to CSV format is very fast and can handle much larger
datasets without a massive slowdown. PDF and Excel export really slows down.
The default CSV export is Unicode that Excel doesn't handle well. Use jump
to URL and render as ASCII CSV. Very very fast and I have done 60,000 rows
that way.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JeffW" <JeffW@.discussions.microsoft.com> wrote in message
news:1E215FB6-73A5-483C-9744-600ACA0D37F0@.microsoft.com...
> Michael:
> Have you received any further feedback on this? I am experiencing a
> similar
> issue when i have a report that returns about 10,000 rows. With less rows
> and a quicker query execution (6 minutes or less) the report comes back.
> -Jeff
> "Michael" wrote:
>> Bruce,
>> Thanks for your reply. Originally I was returning 10,000 rows of data,
>> but I have limited it to 100 rows. I am using 4 query parameters: a
>> start date, an end date, sort by, and hotel property. It is being
>> rendered by the default, HTML. I also want to mention that the report
>> does show up when the query execution time is around 3 minutes or so.
>> Thank you for your help!
>>|||Thanks for the prompt follow up Bruce.
Your point about the amount of data a human might look at is well taken. I
may need to revisit my SQL, as the report i am working with takes the 10,00
rows and transposes them into 15-20 columns of data. So, I really only end
up with a couple hundred rows by 15-20 columns. I've tried using matrix
style reporting for this, but could never really get all the formatting quite
right as i can with just a table style report. I may need to get the data
coming back to resemble the report a bit more closely so RS doesn't need to
do so much processing.
The only odd part is that when i watch the aspnet_wp on the report server as
the db server finsihes up it's query, the worker process does not start
gobbling up memory. It grabs a chunk of cpu for a few seconds, but barely
budges on memory. in the past, i have had exports where the wp maxes out the
memory (1.5 gig of RAM ro so) and then i get the RS out of memory error. For
the report i was originally referring to in this post, this never happens.
Instead, the report just never comes back. It's as if RS never even tried to
return the report.
Also, i did not know that about the csv export. i will begin using that
immediately so thank you for that tip.
-JeffW
"Bruce L-C [MVP]" wrote:
> RS renders in RAM. How much data it handles without major slowdown depends
> on the format. 10,000 rows is not something for a human to look at. That
> much data in my mind is a data export to another program (like Excel).
> Rendering to HTML and to CSV format is very fast and can handle much larger
> datasets without a massive slowdown. PDF and Excel export really slows down.
> The default CSV export is Unicode that Excel doesn't handle well. Use jump
> to URL and render as ASCII CSV. Very very fast and I have done 60,000 rows
> that way.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JeffW" <JeffW@.discussions.microsoft.com> wrote in message
> news:1E215FB6-73A5-483C-9744-600ACA0D37F0@.microsoft.com...
> > Michael:
> >
> > Have you received any further feedback on this? I am experiencing a
> > similar
> > issue when i have a report that returns about 10,000 rows. With less rows
> > and a quicker query execution (6 minutes or less) the report comes back.
> >
> > -Jeff
> >
> > "Michael" wrote:
> >
> >> Bruce,
> >>
> >> Thanks for your reply. Originally I was returning 10,000 rows of data,
> >> but I have limited it to 100 rows. I am using 4 query parameters: a
> >> start date, an end date, sort by, and hotel property. It is being
> >> rendered by the default, HTML. I also want to mention that the report
> >> does show up when the query execution time is around 3 minutes or so.
> >> Thank you for your help!
> >>
> >>
>
>|||Thanks to Bruce and Jeff for the replies, but I am still unsure what to
do to solve my issue. I am only returning 100 rows and the report is
not displaying. In the ReportServer database and in the ExecutionLog,
it shows that the report has completed execution, but there is no
report displaying. What is going on? Please help! Thanks!|||Does it work from the development environment? Where are you displaying this
report, i.e. from your own web page or from Report Manager? If from your own
web page then does the report work from Report Manager?
100 rows should definitely work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <mjhaddad@.gmail.com> wrote in message
news:1119472105.958590.53550@.g44g2000cwa.googlegroups.com...
> Thanks to Bruce and Jeff for the replies, but I am still unsure what to
> do to solve my issue. I am only returning 100 rows and the report is
> not displaying. In the ReportServer database and in the ExecutionLog,
> it shows that the report has completed execution, but there is no
> report displaying. What is going on? Please help! Thanks!
>|||Bruce, it does preview fine in VS .NET. I have deployed the report to
the ReportServer and then try to run it and nothing happens. The same
thing occurs in the ReportManager. Let me know what you think. Thanks!|||Check your credentials datasource for the report.
How fast does it return from the development environment?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <mjhaddad@.gmail.com> wrote in message
news:1119473999.367244.23380@.o13g2000cwo.googlegroups.com...
> Bruce, it does preview fine in VS .NET. I have deployed the report to
> the ReportServer and then try to run it and nothing happens. The same
> thing occurs in the ReportManager. Let me know what you think. Thanks!
>|||Bruce, credentials are fine and the report returns in a little over 10
minutes from the development environment.|||100 rows should not be a problem. Plus you say that if the report comes back
in 3 minutes you are OK. This makes me think that it is an IIS issue. That
IIS is timing out on you. I have heard of people adjusting something in IIS
for this but I am not sure where it is done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <mjhaddad@.gmail.com> wrote in message
news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
> Bruce, credentials are fine and the report returns in a little over 10
> minutes from the development environment.
>|||Bruce, I have adjusted the time out for IIS, but it did not resolve the
issue. I am lost, to be honest with you. Thanks for your help!|||Hi Bruce,
I have a similar issue but much more data on a server with 3G memory. I use
RS to render 200,000 rows to CSV and thought that would be better than PDF,
but it took more than 30 minutes and then threw OutOfMemory exeception and RS
got restarted many times according to Event log. We already raise MaxMemory
to 120 in the config file, but just don't know why RS still can not render a
csv file. We really need to render that much data into a report for clients.
Does anyone know how to use RS deal with that much amount of data? Is there
some other way I can try? Thank you.
Charles
"Bruce L-C [MVP]" wrote:
> 100 rows should not be a problem. Plus you say that if the report comes back
> in 3 minutes you are OK. This makes me think that it is an IIS issue. That
> IIS is timing out on you. I have heard of people adjusting something in IIS
> for this but I am not sure where it is done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael" <mjhaddad@.gmail.com> wrote in message
> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
> > Bruce, credentials are fine and the report returns in a little over 10
> > minutes from the development environment.
> >
>
>|||Michael,
Try to adjust to MaxMemory in RS config file(default is 60) and that may help.
Charles
"Michael" wrote:
> Bruce, I have adjusted the time out for IIS, but it did not resolve the
> issue. I am lost, to be honest with you. Thanks for your help!
>|||I have done about 60,000 rows but they were small (very few columns).
200,000 is just past what RS is designed for. My suggestion is to look at
other technologies to do this (like DTS etc).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
> Hi Bruce,
> I have a similar issue but much more data on a server with 3G memory. I
> use
> RS to render 200,000 rows to CSV and thought that would be better than
> PDF,
> but it took more than 30 minutes and then threw OutOfMemory exeception and
> RS
> got restarted many times according to Event log. We already raise
> MaxMemory
> to 120 in the config file, but just don't know why RS still can not render
> a
> csv file. We really need to render that much data into a report for
> clients.
> Does anyone know how to use RS deal with that much amount of data? Is
> there
> some other way I can try? Thank you.
> Charles
> "Bruce L-C [MVP]" wrote:
>> 100 rows should not be a problem. Plus you say that if the report comes
>> back
>> in 3 minutes you are OK. This makes me think that it is an IIS issue.
>> That
>> IIS is timing out on you. I have heard of people adjusting something in
>> IIS
>> for this but I am not sure where it is done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Michael" <mjhaddad@.gmail.com> wrote in message
>> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
>> > Bruce, credentials are fine and the report returns in a little over 10
>> > minutes from the development environment.
>> >
>>|||Hi Bruce,
Thanks for your reply. Is there any document that mentions about report
render limit for each report format (csv, pdf,...)? Thanks.
Charles
"Bruce L-C [MVP]" wrote:
> I have done about 60,000 rows but they were small (very few columns).
> 200,000 is just past what RS is designed for. My suggestion is to look at
> other technologies to do this (like DTS etc).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
> > Hi Bruce,
> >
> > I have a similar issue but much more data on a server with 3G memory. I
> > use
> > RS to render 200,000 rows to CSV and thought that would be better than
> > PDF,
> > but it took more than 30 minutes and then threw OutOfMemory exeception and
> > RS
> > got restarted many times according to Event log. We already raise
> > MaxMemory
> > to 120 in the config file, but just don't know why RS still can not render
> > a
> > csv file. We really need to render that much data into a report for
> > clients.
> > Does anyone know how to use RS deal with that much amount of data? Is
> > there
> > some other way I can try? Thank you.
> >
> > Charles
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> 100 rows should not be a problem. Plus you say that if the report comes
> >> back
> >> in 3 minutes you are OK. This makes me think that it is an IIS issue.
> >> That
> >> IIS is timing out on you. I have heard of people adjusting something in
> >> IIS
> >> for this but I am not sure where it is done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Michael" <mjhaddad@.gmail.com> wrote in message
> >> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
> >> > Bruce, credentials are fine and the report returns in a little over 10
> >> > minutes from the development environment.
> >> >
> >>
> >>
> >>
>
>|||No, there is not a document. I'm sure it varies depending on the RAM you
have and other configuration issues. In general though I would say the
product is designed around human consumption not data loads. Sometimes a
relatively large amount of data is for use by a person for instance into
Excel. Until recently Excel had a limit of 64,000 which when doing CSV RS
can handle (at least on my box which has 2 gigs of RAM). One thing you might
want to try is to see if the user needs all the columns of the report when
you export to CSV. If not, then you could have an alternate report just for
that. What I do sometimes is have a report for viewing and a report to
exporting. I hide the exporting one in list view and then I add a link with
a jump to URL at the top of the report. It exports in CSV ASCII (very
important, default by RS is Unicode which Excel does not recognize).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:C9BBC97A-7E32-4241-ABF5-177FFA654E04@.microsoft.com...
> Hi Bruce,
> Thanks for your reply. Is there any document that mentions about report
> render limit for each report format (csv, pdf,...)? Thanks.
>
> Charles
> "Bruce L-C [MVP]" wrote:
>> I have done about 60,000 rows but they were small (very few columns).
>> 200,000 is just past what RS is designed for. My suggestion is to look at
>> other technologies to do this (like DTS etc).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
>> news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
>> > Hi Bruce,
>> >
>> > I have a similar issue but much more data on a server with 3G memory. I
>> > use
>> > RS to render 200,000 rows to CSV and thought that would be better than
>> > PDF,
>> > but it took more than 30 minutes and then threw OutOfMemory exeception
>> > and
>> > RS
>> > got restarted many times according to Event log. We already raise
>> > MaxMemory
>> > to 120 in the config file, but just don't know why RS still can not
>> > render
>> > a
>> > csv file. We really need to render that much data into a report for
>> > clients.
>> > Does anyone know how to use RS deal with that much amount of data? Is
>> > there
>> > some other way I can try? Thank you.
>> >
>> > Charles
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> 100 rows should not be a problem. Plus you say that if the report
>> >> comes
>> >> back
>> >> in 3 minutes you are OK. This makes me think that it is an IIS issue.
>> >> That
>> >> IIS is timing out on you. I have heard of people adjusting something
>> >> in
>> >> IIS
>> >> for this but I am not sure where it is done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Michael" <mjhaddad@.gmail.com> wrote in message
>> >> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
>> >> > Bruce, credentials are fine and the report returns in a little over
>> >> > 10
>> >> > minutes from the development environment.
>> >> >
>> >>
>> >>
>> >>
>>|||Hi Bruce,
Currently I use data driven subscription to render CSV file. How do I render
CSV in ASCII from the subscription? Would it be faster and less memory
intensive? Thanks.
Charles
"Bruce L-C [MVP]" wrote:
> No, there is not a document. I'm sure it varies depending on the RAM you
> have and other configuration issues. In general though I would say the
> product is designed around human consumption not data loads. Sometimes a
> relatively large amount of data is for use by a person for instance into
> Excel. Until recently Excel had a limit of 64,000 which when doing CSV RS
> can handle (at least on my box which has 2 gigs of RAM). One thing you might
> want to try is to see if the user needs all the columns of the report when
> you export to CSV. If not, then you could have an alternate report just for
> that. What I do sometimes is have a report for viewing and a report to
> exporting. I hide the exporting one in list view and then I add a link with
> a jump to URL at the top of the report. It exports in CSV ASCII (very
> important, default by RS is Unicode which Excel does not recognize).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:C9BBC97A-7E32-4241-ABF5-177FFA654E04@.microsoft.com...
> > Hi Bruce,
> >
> > Thanks for your reply. Is there any document that mentions about report
> > render limit for each report format (csv, pdf,...)? Thanks.
> >
> >
> > Charles
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I have done about 60,000 rows but they were small (very few columns).
> >> 200,000 is just past what RS is designed for. My suggestion is to look at
> >> other technologies to do this (like DTS etc).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> >> news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
> >> > Hi Bruce,
> >> >
> >> > I have a similar issue but much more data on a server with 3G memory. I
> >> > use
> >> > RS to render 200,000 rows to CSV and thought that would be better than
> >> > PDF,
> >> > but it took more than 30 minutes and then threw OutOfMemory exeception
> >> > and
> >> > RS
> >> > got restarted many times according to Event log. We already raise
> >> > MaxMemory
> >> > to 120 in the config file, but just don't know why RS still can not
> >> > render
> >> > a
> >> > csv file. We really need to render that much data into a report for
> >> > clients.
> >> > Does anyone know how to use RS deal with that much amount of data? Is
> >> > there
> >> > some other way I can try? Thank you.
> >> >
> >> > Charles
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> 100 rows should not be a problem. Plus you say that if the report
> >> >> comes
> >> >> back
> >> >> in 3 minutes you are OK. This makes me think that it is an IIS issue.
> >> >> That
> >> >> IIS is timing out on you. I have heard of people adjusting something
> >> >> in
> >> >> IIS
> >> >> for this but I am not sure where it is done.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Michael" <mjhaddad@.gmail.com> wrote in message
> >> >> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
> >> >> > Bruce, credentials are fine and the report returns in a little over
> >> >> > 10
> >> >> > minutes from the development environment.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It takes the same amount of time. The difference is that Excel recognizes it
as CSV so it does not put it all in one column. This is what I use in jump
to URL. Perhaps it will help you with your subscriptions:
This causes Excel to come up with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:43871106-CE27-47D9-8E6D-66B35B83CF8F@.microsoft.com...
> Hi Bruce,
> Currently I use data driven subscription to render CSV file. How do I
> render
> CSV in ASCII from the subscription? Would it be faster and less memory
> intensive? Thanks.
> Charles
> "Bruce L-C [MVP]" wrote:
>> No, there is not a document. I'm sure it varies depending on the RAM you
>> have and other configuration issues. In general though I would say the
>> product is designed around human consumption not data loads. Sometimes a
>> relatively large amount of data is for use by a person for instance into
>> Excel. Until recently Excel had a limit of 64,000 which when doing CSV RS
>> can handle (at least on my box which has 2 gigs of RAM). One thing you
>> might
>> want to try is to see if the user needs all the columns of the report
>> when
>> you export to CSV. If not, then you could have an alternate report just
>> for
>> that. What I do sometimes is have a report for viewing and a report to
>> exporting. I hide the exporting one in list view and then I add a link
>> with
>> a jump to URL at the top of the report. It exports in CSV ASCII (very
>> important, default by RS is Unicode which Excel does not recognize).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
>> news:C9BBC97A-7E32-4241-ABF5-177FFA654E04@.microsoft.com...
>> > Hi Bruce,
>> >
>> > Thanks for your reply. Is there any document that mentions about report
>> > render limit for each report format (csv, pdf,...)? Thanks.
>> >
>> >
>> > Charles
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I have done about 60,000 rows but they were small (very few columns).
>> >> 200,000 is just past what RS is designed for. My suggestion is to look
>> >> at
>> >> other technologies to do this (like DTS etc).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
>> >> news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
>> >> > Hi Bruce,
>> >> >
>> >> > I have a similar issue but much more data on a server with 3G
>> >> > memory. I
>> >> > use
>> >> > RS to render 200,000 rows to CSV and thought that would be better
>> >> > than
>> >> > PDF,
>> >> > but it took more than 30 minutes and then threw OutOfMemory
>> >> > exeception
>> >> > and
>> >> > RS
>> >> > got restarted many times according to Event log. We already raise
>> >> > MaxMemory
>> >> > to 120 in the config file, but just don't know why RS still can not
>> >> > render
>> >> > a
>> >> > csv file. We really need to render that much data into a report for
>> >> > clients.
>> >> > Does anyone know how to use RS deal with that much amount of data?
>> >> > Is
>> >> > there
>> >> > some other way I can try? Thank you.
>> >> >
>> >> > Charles
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> 100 rows should not be a problem. Plus you say that if the report
>> >> >> comes
>> >> >> back
>> >> >> in 3 minutes you are OK. This makes me think that it is an IIS
>> >> >> issue.
>> >> >> That
>> >> >> IIS is timing out on you. I have heard of people adjusting
>> >> >> something
>> >> >> in
>> >> >> IIS
>> >> >> for this but I am not sure where it is done.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Michael" <mjhaddad@.gmail.com> wrote in message
>> >> >> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
>> >> >> > Bruce, credentials are fine and the report returns in a little
>> >> >> > over
>> >> >> > 10
>> >> >> > minutes from the development environment.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Bruce,
Thank you for your hints. But still wondering why RS uses so much memory for
rendering a big report. Have you heard this would be improved in Sql Server
RS 2005?
Charles
"Bruce L-C [MVP]" wrote:
> It takes the same amount of time. The difference is that Excel recognizes it
> as CSV so it does not put it all in one column. This is what I use in jump
> to URL. Perhaps it will help you with your subscriptions:
> This causes Excel to come up with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:43871106-CE27-47D9-8E6D-66B35B83CF8F@.microsoft.com...
> > Hi Bruce,
> >
> > Currently I use data driven subscription to render CSV file. How do I
> > render
> > CSV in ASCII from the subscription? Would it be faster and less memory
> > intensive? Thanks.
> >
> > Charles
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, there is not a document. I'm sure it varies depending on the RAM you
> >> have and other configuration issues. In general though I would say the
> >> product is designed around human consumption not data loads. Sometimes a
> >> relatively large amount of data is for use by a person for instance into
> >> Excel. Until recently Excel had a limit of 64,000 which when doing CSV RS
> >> can handle (at least on my box which has 2 gigs of RAM). One thing you
> >> might
> >> want to try is to see if the user needs all the columns of the report
> >> when
> >> you export to CSV. If not, then you could have an alternate report just
> >> for
> >> that. What I do sometimes is have a report for viewing and a report to
> >> exporting. I hide the exporting one in list view and then I add a link
> >> with
> >> a jump to URL at the top of the report. It exports in CSV ASCII (very
> >> important, default by RS is Unicode which Excel does not recognize).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> >> news:C9BBC97A-7E32-4241-ABF5-177FFA654E04@.microsoft.com...
> >> > Hi Bruce,
> >> >
> >> > Thanks for your reply. Is there any document that mentions about report
> >> > render limit for each report format (csv, pdf,...)? Thanks.
> >> >
> >> >
> >> > Charles
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I have done about 60,000 rows but they were small (very few columns).
> >> >> 200,000 is just past what RS is designed for. My suggestion is to look
> >> >> at
> >> >> other technologies to do this (like DTS etc).
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> >> >> news:A08B17BA-1720-4324-A1ED-96B659CDBAE7@.microsoft.com...
> >> >> > Hi Bruce,
> >> >> >
> >> >> > I have a similar issue but much more data on a server with 3G
> >> >> > memory. I
> >> >> > use
> >> >> > RS to render 200,000 rows to CSV and thought that would be better
> >> >> > than
> >> >> > PDF,
> >> >> > but it took more than 30 minutes and then threw OutOfMemory
> >> >> > exeception
> >> >> > and
> >> >> > RS
> >> >> > got restarted many times according to Event log. We already raise
> >> >> > MaxMemory
> >> >> > to 120 in the config file, but just don't know why RS still can not
> >> >> > render
> >> >> > a
> >> >> > csv file. We really need to render that much data into a report for
> >> >> > clients.
> >> >> > Does anyone know how to use RS deal with that much amount of data?
> >> >> > Is
> >> >> > there
> >> >> > some other way I can try? Thank you.
> >> >> >
> >> >> > Charles
> >> >> >
> >> >> > "Bruce L-C [MVP]" wrote:
> >> >> >
> >> >> >> 100 rows should not be a problem. Plus you say that if the report
> >> >> >> comes
> >> >> >> back
> >> >> >> in 3 minutes you are OK. This makes me think that it is an IIS
> >> >> >> issue.
> >> >> >> That
> >> >> >> IIS is timing out on you. I have heard of people adjusting
> >> >> >> something
> >> >> >> in
> >> >> >> IIS
> >> >> >> for this but I am not sure where it is done.
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Bruce Loehle-Conger
> >> >> >> MVP SQL Server Reporting Services
> >> >> >>
> >> >> >> "Michael" <mjhaddad@.gmail.com> wrote in message
> >> >> >> news:1119475785.897599.226260@.o13g2000cwo.googlegroups.com...
> >> >> >> > Bruce, credentials are fine and the report returns in a little
> >> >> >> > over
> >> >> >> > 10
> >> >> >> > minutes from the development environment.
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||What about matrix report?
I have about 150.000 records which make my matrix report 150 rows and
90 columns.
It does not render to excel:( because of low resources.
please help|||Is this the number of rows returned or the size of the table. It doesn't
matter how big the table is (I go against tables with 50 million rows), what
matters is the number of rows returned. If you have that many rows returned
then you need to look at your query and have the database do more of the
work. The best strategy (not just for RS but for any application) is to
return the smallest number of rows possible. Have SQL Server DB do the work,
not RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kamel" <kwiciak@.gmail.com> wrote in message
news:1119644816.435274.296140@.g43g2000cwa.googlegroups.com...
> What about matrix report?
> I have about 150.000 records which make my matrix report 150 rows and
> 90 columns.
> It does not render to excel:( because of low resources.
> please help
>|||My query returns at the moment about 150.000 - 200.000 records.
I use this data on my matrix report.
I have 3 levels of grouping on rows and 3 levels of grouping on columns
and in cells I have also some calculations.
Finally I came up with matrix with about 150 rows and 90 columns.
I does not want to render to excel.

Monday, March 26, 2012

Report connection fails with DNS name

Hi,

I am facing problem while setting connection string in Report Service project.
Problem is when I specify DNS name instead of machine name as the Server in the connection string.

When I specify DNS name in the connection string it gives error that "A connection cannot be made.Ensure that the server is running" on click of "Test Connection"
Also I am not able to view reports directly through IE. I.e. http://<DNS_NAME>/Reports.
When I access Reports, it prompts me to enter username/password. Even after specifying valid username, it shows me the prompt again n again.

Is there some known issue with Reporting Service or with IIS setting not able to connect to a DNS name? I did not face with any issue when
I specified DNS name in the connection string of Analysis Service project.

Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service?

We also verified that:
1: Ping with both machine name and DNS name returns same result.
2: nslookup with both machine name and DNS name resolves to same IP.

3: Tried putting the DNS name in the hosts file.

Eagerly awaiting some positive reply.

Note: I am working with SQL Server 2005 Standard Edition.

No replies yet Sad

We are stuck up with this issue since quite some time.

still waiting for some positive inputs.

|||

After doing some investigation, I have found that the issue is with Analysis server not able to connect using the DNS name (Note: my Reporting Service proj is connecting to Analysis services datasource). When I connect to Analysis server through SQL Server management studio (by def which uses Win authentication), and specify machine name (ABC) in Server then it connects but if I give DNS name (XYZ.PWR.COM) it gives me the following error:

************************************************************************************************************
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

************************************************************************************************************

If I connect to Reporting Service using DNS name then I get the following error:

************************************************************************************************************
===================================

Cannot connect to XYZ.PWR.COM.

===================================

A connection to the report server failed. Most likely, you do not have permission to connect to the computer. You should either try to connect using a report server URL for the Server name, or if the Distributed COM Users group exists on the remote computer, verify that you are a member. (Microsoft.SqlServer.Management.UI.RSClient)

Program Location:

at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
************************************************************************************************************

When I ping with machine name (ABC) and DNS name (XYZ.PWR.COM), both resolves to the same IP.
C:\Documents and Settings\Administrator>ping ABC

Pinging ABC.corp.abctech.com [xx.x.xx.xx] with 32 bytes of d

C:\Documents and Settings\Administrator>ping XYZ.PWR.COM

Pinging XYZ.PWR.COM [xx.x.xx.xx] with 32 bytes of data:

Is this problem because of the kerberos authentication that Analysis server uses to authenticate?

I am able to connect to the Database engine using DNS name (but using SQL Server authentication).
Also I was able to view the reportserver and reports page by changing the IIS authentication to NTLM.
But finally I am not able to view the reports.

Can anyone please help me in solving this issue.

Awatiitng some positive reply.

Report connection fails with DNS name

Hi,

I am facing problem while setting connection string in Report Service project.
Problem is when I specify DNS name instead of machine name as the Server in the connection string.

Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service?

We also verified that:
1: Ping with both machine name and DNS name returns same result.
2: nslookup with both machine name and DNS name resolves to same IP.

3: Tried putting the DNS name in the hosts file.

Eagerly awaiting some positive reply.

Note: I am working with SQL Server 2005 Standard Edition.

No replies yet Sad

We are stuck up with this issue since quite some time.

still waiting for some positive inputs.

|||

Program Location:

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

************************************************************************************************************

If I connect to Reporting Service using DNS name then I get the following error:

************************************************************************************************************
===================================

Cannot connect to XYZ.PWR.COM.

===================================

Program Location:

When I ping with machine name (ABC) and DNS name (XYZ.PWR.COM), both resolves to the same IP.
C:\Documents and Settings\Administrator>ping ABC

Pinging ABC.corp.abctech.com [xx.x.xx.xx] with 32 bytes of d

C:\Documents and Settings\Administrator>ping XYZ.PWR.COM

Pinging XYZ.PWR.COM [xx.x.xx.xx] with 32 bytes of data:

Is this problem because of the kerberos authentication that Analysis server uses to authenticate?

Can anyone please help me in solving this issue.

Awatiitng some positive reply.

Tuesday, March 20, 2012

Report Builder FQDN Issue

Ok this one has me stuffed.

SQL 2005 Server with all the fun stuff aswell.

HOWEVER, when attempting to run the report builder it fails when using a FQDN. (Full Qualified Domain Name)

http:\\servername\reports - No Issues

http:\\ipaddress\reports - No Issues

http:\\servername.domain.com.au\reports - Can Access, reports run fine but fails to run the report builder.

The error deatails are as follows:

ERROR DETAILS
Following errors were detected during this operation.
* [3/08/2006 11:21:37 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://SQLSVR.nat.company.com.au/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The operation has timed out
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)

Further to this, i can not open the report server from the Management Studio on the local machine.

Here is the error:

===================================

Cannot connect to SQLSVR\SQL2005.

===================================

The operation has timed out (System.Web.Services)

Program Location:

at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at Microsoft.SqlServer.Management.UI.RSClient.RSClientConnection.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.SqlServer.Management.UI.RSClient.RSClientConnection.CreateConnection(String connectionString)
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.CreateConnectionObject()
at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Please Help.

Any Ideas on where to go would be very much appreciated.

Thanks in advance

Report Builder Fails to Launch

Good day:

I've searched and found a whole lot of information regarding Report Builder's occasional failures to launch, but every single suggestion has not worked for me. I'll try to describe this as completely as possible.

PC 1: Login with my user id, navigate to either my test or production report server, click 'Report Builder'; application launches and can be used as expected. Login with a 2nd user id, and it works. OS is Win2003 Server. Because both logins work on this PC, which tells me the security credentials are set correctly.

PC2: Login with my user id, navigate to either my test or production report server, click 'Report Builder'; application fails to launch. I do not receive an error message or dialog. The 'frames' for the Report Builder form flash momentarily on the screen. Login with same 2nd user id, and it fails the same way. OS is WinXP SP2; all updates.

PC3: Login with my user id, navigate to either my test or production report server, click 'Report Builder'; application fails to launch. I do not receive an error message or dialog. The 'frames' for the Report Builder form flash momentarily on the screen. Login with same 2nd user id, and it fails the same way. OS is WinXP SP2; all updates.

On PC2 and PC3, I navigate to the ClickOnce cache in my profile, right-click and 'run as' the reportbuilder.exe file. Choosing 'run as' my login and providing a password -- voila -- the application runs. I have to manually set the link to the report server, but it appears all else works fine. Same situation when I use my 2nd user id. If I don't 'run as' and provide the password (just double-click to launch the file), I get the same 'flash frames' above.

Ultimately, the push is to get PC2 and PC3 to function as does PC1. My resident .NET expert is stumped, as am I. Please help.

I must not have provided enough information. What do I need to post in order to explain this situation better?

|||

The troubleshooting has begun.

WinXP SP2 w/o all the updates or .NET. Click 'Report Builder', which errors that I don't have .NET 2.0. Download and install redistributable package -- voila -- report builder works. Note there is nothing else on the box except WinXP SP2 and .NET 2.0.

This leads me to conclude that there's something in one of the patches from MIcrosoft that is preventing the report builder from working. PC2 ( from my original post ) has all the latest patches as well as a bunch of applications. PC3 has all the latest patches with SQL Server 2000 SP4 and Office 2007. Neither PC2 nor PC3 can run Report Builder. I'm now on PC4.

It's either a patch from microsoft causing this issue, or the order in which the patches were applied. I'm going to be testing the Report Builder after every installed patch ( yes -- I am that interested in getting to the bottom of this that I'd download over 75 patches one at a time ).

If I find anything, I'll post again.

|||

So apparently the software for a Kensington Trackball will prevent a .NET 2.0 ClickOnce application from launching. Like Report Builder. No kidding. I've uninstalled the tracball software and Report Builder works. Reinstalled the tracball software, Report Builder fails to launch. Uninstall the tracball software (leaving it off this time) Report Builder works.

Nice.

Tuesday, February 21, 2012

replications fails to bulk copy out of table.

In the last two weeks the scheduled replication has
failed on the same table. Our programmer has not changed
the table. Manual replications are successful.
Error message: The process could not bulk copy out of
table '[dbo].[syncobj_0x4436433341414546]'.
Error details: I/O error while writing BCP data-file
(source: ODBC SQL Server Driver [ODBC]; Error number: 0)
this is an OS level problem. Do you have disk space problems? Hard disk
problems? Is this error reproducible?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Belen" <anonymous@.discussions.microsoft.com> wrote in message
news:313001c470e1$6f7c5bf0$a501280a@.phx.gbl...
> In the last two weeks the scheduled replication has
> failed on the same table. Our programmer has not changed
> the table. Manual replications are successful.
> Error message: The process could not bulk copy out of
> table '[dbo].[syncobj_0x4436433341414546]'.
> Error details: I/O error while writing BCP data-file
> (source: ODBC SQL Server Driver [ODBC]; Error number: 0)
>
|||There is enough disk space on the server. The problem
has corrected itself but what could have been the problem.
>--Original Message--
>this is an OS level problem. Do you have disk space
problems? Hard disk
>problems? Is this error reproducible?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Belen" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:313001c470e1$6f7c5bf0$a501280a@.phx.gbl...
changed[vbcol=seagreen]
0)
>
>.
>
|||I don't know. Other people have reported it. Perhaps an aggressive
anti-virus software.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:6c2301c475a9$63782ec0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is enough disk space on the server. The problem
> has corrected itself but what could have been the problem.
> problems? Hard disk
> message
> changed
> 0)

Friday, March 30, 2012

Report Does Not Render!

Monday, March 26, 2012

Report connection fails with DNS name

Report connection fails with DNS name

Tuesday, March 20, 2012

Report Builder FQDN Issue

Report Builder Fails to Launch

Tuesday, February 21, 2012

replications fails to bulk copy out of table.

Importing Error

Blog Archive

About Me