Solved

Excel export not happening for more than 4 mb data in ASP and vbscript.

Posted on 2009-06-29
9
816 Views
Last Modified: 2012-05-07
Hello Experts,
Here is the scenario:
I have a website in ASP/ vbscript, IIS 6.0
I run a query in Oracle 10g and get the data in an array on the front end (ASP).
The array is then used to export data in excel and shown to users.
It works fine for excel size less than 4 mb but if the excel size increases to more than 4 mb I get the error: Page cannot be displayed.
I was wondering if there is some setting that i need to do for allowing the export of data to excel which is more than 4 mb.
Any suggestions are welcomed.
Thank You.
0
Comment
Question by:Faheem Shaikh
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:tobzzz
ID: 24736557
Have you tried these type of setting? Note, figures are just example numbers.
<%

Session.Timeout=30

Server.ScriptTimeout=90

Response.Expires=0

%>

Open in new window

0
 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24736696
Hi TOBZZZ-
Thanks for looking into this, but its not a time out issue. But I will give this a try too.
 
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24736750
Also check if the data you are exporting is exceeding excel's limit of 65536 rows in a worksheet
0
 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24736779
The max number of records is just 2000. It will never exceed to more than that. So excel's limit is never reached.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Expert Comment

by:bmatumbura
ID: 24736798
If the data results in more than 65536 rows when exported to excel, you may have to partition the data into multiple excel sheets. Excel can only take a maximum of 65536 rows in a worksheet and 256 worksheets in a workbook.
0
 
LVL 11

Expert Comment

by:tobzzz
ID: 24737177
if you just write it to screen, not create as an excel doc, does it still time out? If it was me, I would run the query and display on screen to eliminate if it is writing to excel that's the problem.
0
 
LVL 11

Expert Comment

by:tobzzz
ID: 24737191
sorry, not time out, but fail...... does it still fail?
0
 
LVL 8

Accepted Solution

by:
lharrispv earned 500 total points
ID: 24737536
I think I might know what your problem is.  Google for AspMaxRequestEntityAllowed and Asp Response buffer.

A few years back I was trying to do something very similar and I had to up those values in the metabase.xml file (IIS 6).  For IIS 5 it is not as easy but can still be changed.

In IIS 6 MS made smaller the amount of data that can be transfered via the http response for security reasons.  I think 4 mg is around near the new default.
0
 
LVL 8

Author Comment

by:Faheem Shaikh
ID: 24742971
Hello lharrispv,
That is exactly what i was looking for. My Sys Admin also pointed me towards the same.
I have set the AspBufferingLimit="26214400" --> 25 MB in metabase.xml file at c:\Windows\System32\Inetsrv. The default was 4 MB. AspBufferingLimit is used to set the Response limit.
Also, AspMaxRequestEntityAllowed  is actualy used to set the upload file limit to the server i.e. Request limit.
Thank You.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now