Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Timing out when exporting to excel Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2010 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 7:16 AM
Points: 130, Visits: 173
I am trying to export a report that is returning over 65000 records to excel. I have created a grouping Int((RowNumber(Nothing)-1)/65000) and also told it to create a new page after 65000. But the report takes too long to export and just times out. Does anyone have any ideas? Any help would be greatly appreciated.
Post #951764
Posted Tuesday, July 13, 2010 1:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
Seems like you're using the wrong tool for the job. More than likely you're bumping up against the timeout for either a) the Database, connection etc, b) the reportserver's rendering timeout, c) IIS's timeouts etc... Perhaps if you elaborate on the business rules/reasons you're doign this we might be able to offer a suggestion for you.

Do you just need to get the data out? will XML or a CSV do? If you so might be better servers with BCP instead of SSRS. Will you users actually take the time to analyze 65k+ rows? probably not. I'm guessing this is for an import into another system, which is why I suggested BCP. Alternatively, you could use SSIS to output your data to another datasource, Txt file or others.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #951784
Posted Tuesday, July 13, 2010 2:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 7:16 AM
Points: 130, Visits: 173
We have a report in place that users can run through SharePoint. When the report is ran they export the data to excel so they can do some pivoting and sorting. When they are trying to export a large amount of data it just times out and never finishes the export to excel. It take hours before it finally fails. Can this be fixed somehow?
Post #951825
Posted Wednesday, July 14, 2010 6:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
Again, you're probably bumping up against some timeout somewhere along the lines, or perhaps even running the server/workstation out of disk space as it tries to create a really big excel document.

If all they are doing is exporting the data so they can sort/pivot/aggregate it, why not do that in the report for them? Why have the extra step of exporting all of that data just to have most of it tossed aside by aggregation?

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #952249
Posted Wednesday, July 14, 2010 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 7:16 AM
Points: 130, Visits: 173
I completely agree with you. I guess my next step would be to learn how to do pivoting in a report. Any ideas on where I could look?
Post #952341
Posted Wednesday, July 14, 2010 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
There are a couple of ways to do it, first, you could just do the pivot in your Stored Procedure or SQL Code for your dataset. This would limit the amount of data you need to pull across the network from your database to reporting server (if they are on separate machines that is) For ideas on how to do it in TSQL Code, you can take a look at JEff's article on this topic here: http://www.sqlservercentral.com/articles/T-SQL/63681/.

Alternatively, you could use a matrix (in SSRS 05) or tablix(in 08) and use column and row grouping options in much the same way you would in excel to create a pivot table. This may be a bit easier, but will probably be the slower of the two methods due to network traffic and processing time on the Report server.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #952357
Posted Wednesday, November 7, 2012 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 2:26 PM
Points: 6, Visits: 8
I'm out looking for a solution for this same issue and came across your post. Sometimes users want a simple report that they can slice and dice to thier own needs. I build reports for our finance team all the time that get turned upside down and inside out depending on what they're looking for. There shouldn't be an issue exporting to Excel. If the report didn't time out when executing, the export to Excel should have been simple.

One of the previous times we ran into this issue there was an Excel file stored in a temp directory that was locked and SSRS wouldn't export another until that file was deleted. Don't know what happened there but it's worth a shot to clean up your temp files. One of our locations has run into this again and it appears this isn't the issue this time so we'll keep looking. Multiple users can run the report and export in seconds but our tech at this site and the user he's helping both have an issue exporting. This tells me it's workstation specific.

My point is, don't give up. There's something wrong if you can't export to Excel. You'll face this again sooner or later with another report. May as well have the know how to fix it when you do. imho
Post #1382181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse