exporting reports times out or freezes pc

i am trying to export a somewhat large report to excel
it is 25,000 rows of data
when running it from the report server website i get "Server Application Unavailable"
but all other smaller reports work fine
i tried changing timeout values everywhere, in config files, connection strings etc...
when i run it from visual studio it eats up over 300mb and never saves and freezes my computer
what gives?Good Evening!
I know when exporting to Excel it "burps" at anything > 65.535 rows - but I do not know the column limitation!
I know I am not any help here - but
"Server Application Unavailable" - what do you mean - you go tohttp://localhost/reports and then execute directly from MS RS .
or
Is there a document map in the MS RS report?
or
"Server Application Unavailable" seems a little strange to me - so
You select the MS RS report to execute - it executes - you select export <excel> the dialor box comes up to save, you say ok and define where you want to save and then it just hangs?
??
Send some info back - I am curious
best regards,





|||it is about 25,000 rows
there is no document map
your last statement is precise, it executes, is displayed in the web browser, then when i export it it waits a little bit and then says the error message i stated before
smaller reports work with no problem|||

I like these weird problems!

Have you installed the service packs for MS RS?

There are two(2) SP1, and SP2..

Another question, when exporting - are you exporting from the server to another PC in the network or just exporting from the server to an excel file on the server?

|||i don't know how weird the problem is, i think reporting services just can't handle this
also when i try to export the report in visual studio it just eats up tons of memory and freezes the machine
i have all the latest service packs
i am exporting the file to save on my hard drive, i am running RS from my laptop and sql server from a real server|||Hi,
Iam facing the same problem when I try to export to PDF format the same thing happens it freezes the system.
the senario here is Reporting server is installed on a server machine & Iam accessing the reports from windows XP client. MSRS 2000 with service pack 2 is intalled on the server.
Another imp issue is Iam unable to print. my report has 4,250 pages with approximately 50 - 55 records in a page Iam trying to print its not printing I even tried to print only page 1-1 (single page) . it shows the "Printing Now..." dialog but after 5 mins it fails.
I've tried the same thing, same environment at different place where I was having 2500 pages printing was working fine. can anybody tell me what is the solution for this.
Regards

Adonis

Exporting report (excel vs CSV)

Exporting csv runs faster and doesn't have limitation of rows compared with
the exporting excel.
However, I have issues using CSV.
Exporting csv do not show all column ( in case I use "Hidden" function on
the report.)
Exporting csv do not display correctly the Header on the table ( in case I
use field value (for example "=Fields!customHeader1.Value") for Header on
the table)
Is there any ways to export the report as csv and display correctly even
though I use some functions?Hi Ken,
Here is a link that might help clear up some of your issues.
http://blogs.msdn.com/bimusings/archive/2007/02/07/reporting-services-why-ar
en-t-all-my-report-columns-exporting-to-csv-and-or-xml.aspx
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Ken" <klee@.jeromegroup.com>
> Subject: Exporting report (excel vs CSV)
> Date: Thu, 27 Dec 2007 16:07:35 -0600
> Exporting csv runs faster and doesn't have limitation of rows compared
with
> the exporting excel.
> However, I have issues using CSV.
> Exporting csv do not show all column ( in case I use "Hidden" function on
> the report.)
> Exporting csv do not display correctly the Header on the table ( in case
I
> use field value (for example "=Fields!customHeader1.Value") for Header on
> the table)
>
> Is there any ways to export the report as csv and display correctly even
> though I use some functions?
>
>

exporting multiple rows

I'm trying to set up a row that has 5 rows below it and when you click on the + it'll expand to show the five lines.

They're all set up to toggle based on the one row. It works online, but when I try to export it to anything, it only picks up the first detail row and leaves off the rest. I'm doing this for a bunch of reports, and they're all being emailed using subscriptions.

I've moved the detail rows around but each time it's just the first detail row that is exported.

Any ideas?

thx,

M@.

mattcushing@.gmail.com(noSpam)

Hello,

What formats are you trying to export to? Toggleable visibility is only supported in a few rendering extensions (HTML, Winforms, and in some cases Excel).

When you export to PDF or Image, those renderers honor the toggle state at the time the file is exported. So, if you have all of your toggleable items collapsed and then export to PDF you will only see the collapsed representation.

-Chris

sql

exporting more tha 65k rows to excel

hi all,

I wanted to know how to export a report that returns more than 65,000 rows of data to MS Excel, it will fail because excel has a limit of 65K records per excel sheet.

What i want to know if there is some way to use multiple sheets (in the same excel file) to show more than 65K records

Eg we have 105K rows of data ...can we export 65K records in the first sheet, and the remaining 40K in the second sheet (of the same excel file)?

Appreciate the Help,

Siddharth

Hello Siddharth,

Using a Table, you can force an explicit page break after a certain number of rows. Because the Excel renderer creates a new worksheet for every explicit page break, you can use this technique to make sure no more than 65,536 rows are exported to a single sheet.

Create an outer table group using this group expression: =Int((RowNumber(Nothing)-1)/65000). Set Page break at end on the group.

Keep in mind that there isn't a one-to-one relationship between SSRS table rows and Excel rows unless the table is the only report item in the report body. So, if you have other report items in addition to your table you will have to also take that into account.

-Chris

|||

Hi Chris,

I knew page break created a new sheet in excel but didnt know how to do it. thanks a lot for thehelp its woking. appreciate it.

siddharth

sql

exporting large amounts of records (50K rows) to excel - painfully slow

We're trying to do an export to excel of a large amount of data.
When we do 100 even 4000 rows it happens in a decent amount of time.
However, when we try to export say 35,000 rows it never finishes and
appears to hang after a long amount of time (15-20 minutes).
Does anyone have any tips for optimizing or troubleshooting this issue.
We're using SQL Server 2000 and Reporting Services 2000.
Thanks,
JasonI use Access Data Projects for real enterprise level reporting; all the
time-- and I push out this many records without a problem.
Maybe you should consider having a better strategy; and choosing real
tools-- like Access Data Projects... instead of SSRS; where version
2005 is STILL considered a beta.
-Aaron
jason.harris@.gmail.com wrote:
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason|||RS 2005 is faster but the following technique will work. You need to export
in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
handle. In RS 2005 you can configure RS to export as ASCII. What I do is
have a link they click on and use the following URL to export in CSV. Order
of magnitudes faster.
="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
here" &casesensitiveparamname=" & Parameters!Paramname.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
When you play with this you might decide to design a report just for
exporting.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jason.harris@.gmail.com> wrote in message
news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> RS 2005 is faster but the following technique will work. You need to
> export in CSV. However, with RS 2000 it exports it as Unicode which
> Excel can't handle. In RS 2005 you can configure RS to export as
> ASCII. What I do is have a link they click on and use the following
> URL to export in CSV. Order of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
It's also useful to remember that RS is a tool to render usable reports to
view in a browser. It also has the option to export those reports. It was
never designed to be used as a data extractor|||Bruce
I have a similar problem but how do I put this URL into the RS Folder.
Thanks
Karen
Bruce L-C [MVP] wrote:
> RS 2005 is faster but the following technique will work. You need to export
> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
> have a link they click on and use the following URL to export in CSV. Order
> of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <jason.harris@.gmail.com> wrote in message
> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> > We're trying to do an export to excel of a large amount of data.
> >
> > When we do 100 even 4000 rows it happens in a decent amount of time.
> >
> > However, when we try to export say 35,000 rows it never finishes and
> > appears to hang after a long amount of time (15-20 minutes).
> >
> >
> > Does anyone have any tips for optimizing or troubleshooting this issue.
> >
> > We're using SQL Server 2000 and Reporting Services 2000.
> >
> > Thanks,
> > Jason
> >|||it's alst important to note that since it's EXCEL we're talking about;
you're gonna have a half dozen ninja spreadsheet FAGS that try to build
ETL tools out of Excel.
In the real world; people use Excel as a word processor and a ETL tool;
just because the idiots dont know any other tools.
is it their fault? no it is the fault of the managers and teachers that
make Excel an expectation.
I would just reccomend shooting anyone that uses Excel for anything.
and then uninstalling Excel from every machine in the house.
Asher_N wrote:
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> > RS 2005 is faster but the following technique will work. You need to
> > export in CSV. However, with RS 2000 it exports it as Unicode which
> > Excel can't handle. In RS 2005 you can configure RS to export as
> > ASCII. What I do is have a link they click on and use the following
> > URL to export in CSV. Order of magnitudes faster.
> >
> > ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> > report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> > "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >
> > When you play with this you might decide to design a report just for
> > exporting.
> >
> >
> It's also useful to remember that RS is a tool to render usable reports to
> view in a browser. It also has the option to export those reports. It was
> never designed to be used as a data extractor|||This is for using the Jump To URL. If you wanted a report that just
consisted of this then you would have a report with textboxes. Properties of
the textbox, navigation, jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1160044319.003258.210730@.b28g2000cwb.googlegroups.com...
> Bruce
> I have a similar problem but how do I put this URL into the RS Folder.
> Thanks
> Karen
> Bruce L-C [MVP] wrote:
>> RS 2005 is faster but the following technique will work. You need to
>> export
>> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
>> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
>> have a link they click on and use the following URL to export in CSV.
>> Order
>> of magnitudes faster.
>> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
>> report
>> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> When you play with this you might decide to design a report just for
>> exporting.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <jason.harris@.gmail.com> wrote in message
>> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
>> > We're trying to do an export to excel of a large amount of data.
>> >
>> > When we do 100 even 4000 rows it happens in a decent amount of time.
>> >
>> > However, when we try to export say 35,000 rows it never finishes and
>> > appears to hang after a long amount of time (15-20 minutes).
>> >
>> >
>> > Does anyone have any tips for optimizing or troubleshooting this issue.
>> >
>> > We're using SQL Server 2000 and Reporting Services 2000.
>> >
>> > Thanks,
>> > Jason
>> >
>

Exporting data from SQL table to Excel File - How to delete rows before inserting new

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Quote:

Originally Posted by LimaCharlie

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Create a view that limits number of exported rows and use it in DTS package instead of a table.

Good Luck.|||hi iburyak,

just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.

thanks.|||Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.

You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.|||Hi Iburyak,

The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.

Any help on that.

Thanks in advance..

Exporting data from SQL table to Excel File - How to delete rows before insertin

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1

ddave|||LimaCharlie, take a look at this post:

http://www.dbforums.com/showthread.php?t=981661

Cheers

kbk|||hi kbk,

that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?

thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.

I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

1. Create an activeX script as below: -This will remove the worksheet.

Function Main()

Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet

srccsvfile = "C:\Test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble

objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)sql

Exporting Data from SDF Database to CSV file

Hi guys,

Would any of you be able to provide some guide on how am I going to export the selected data (multiple rows and columns) into Excel or CSV file?

Or at least export into Text file, which I can later on save the file name as .CSV, so it become a CSV file after saving.

Thanks.

Regards,

Jenson

In which context is the data "selected". You can always enumerate your data and use the System.IO namespace to create the CSV file in code?|||

Hi Erik,

Yes Erik, that's what I intend to do, do you have any guide for me to follow, or any sample codes to refer to? I need to refer to them and write my own one, as I don't really think what I wanted to do is the same, I just need to structure and check how they do it, and what are the various ways to achieve the same thing.

Thanks.

Regards,

Jenson

|||

Hope this sample is what you are looking for:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlServerCe;

namespace ExportSDF

{

class Program

{

static void Main(string[] args)

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

try

{

// Based on this sample: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx

// Open the connection and create a SQL command

//

conn = new SqlCeConnection(@."Data Source = C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf;max database size=256");

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM Customers", conn);

rdr = cmd.ExecuteReader();

System.IO.TextWriter stm = new System.IO.StreamWriter(new System.IO.FileStream(@."C:\customers.csv", System.IO.FileMode.Create), Encoding.Default);

// Iterate through the results

//

while (rdr.Read())

{

// Write all fields except the last one...

for (int i = 0; i < rdr.FieldCount-2; i++)

{

if (rdr[i] != null)

{

stm.Write(rdr[i].ToString());

stm.Write(";");

}

else

{

stm.Write(";");

}

}

if (rdr[rdr.FieldCount-1] != null)

{

stm.Write(rdr[0].ToString());

}

stm.Write(System.Environment.NewLine);

}

// Always dispose data readers and commands as soon as practicable

//

stm.Close();

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

//

conn.Close();

}

}

}

}

Happy coding!

|||Hi Erik,

Thanks for the great reply and sorry for the late reply. The reason behind is I have finished the workaround for this requirements and it's working just fine. By looking at your code, I find that this code is even easier to understand!

I will try it out later as I'm currently busy with other stuff. Thanks for the great codes, Erik!

Have marked your answer as answer =)

Exporting Data from Reporting Services into Multiple Excel Worksheets

Hi,

Please help me.

I need to export around 1 million records to excel. As we know that there is a limit of approx 65,000 rows in one worksheet so the exporting functionality is failing. One way is that I should be able to configure the Reporting Service in such a way so that once 65,000 records gets populated in the first worksheet, then the rest 35,000 records gets popultaed in the second worksheet.

I am not able to find out how this can be achieved in Reporting Services. Please help me in this.

Thanks in advance.

Regards

Raman

Hello Raman,

Take a look at this link, in the 'Page Breaks' section, it may help you.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

It requires that you set a group with a group expression on X number of rows, then you page break on the group.

Hope this helps.

Jarret

Exporting CR from VB

am trying t export a report from CR ver 7.0 from VB 6.0
the report has 16000 rows, but its exporting only 8000+ records...
no error messages.
can anyone help me out.r u exporting to excel, if yes
try using

Report.Export

With Report.ExportOptions
.ExcelExportAllPages
End With

****where report is object of CRAXDRT.report

Dim Report As CRAXDRT.Report|||thanks sraheem for ur help.

yeah am trying to export to excel only.

i tried CRAXDRT

but i coud not find the option .ExcelExportAllPages with ExportOptions|||so sad to know that noone is there with a solution.

i thot its a simple problem and i didnt knew to solve it.

if anyone finds it out,...please let me know,sql

Exporting a report to Excel

I am trying to export a report for SQL Reporting Services Report to
Excel. Unfortunately the number of rows in my report are greater that
the maximum allowable 65536 rows in an Excel sheet. Does anyone have a
workaround that would allow for a SQL Reporting Services Report to be
exported to Excel spread out over multiple sheets to make up for the
65536 row limitation?If you have groups in your report, you can set the Group page break options
on the grouping ans sorting tab. When exporting to Excel, each group becomes
a separate
worksheet in the workbook file. I don't recall what the worksheet limit is.
The one problem is that you cannot title each page, so they come out as
sheet1, sheet2, ... sheet(n)
"jbaldwin00@.gmail.com" wrote:
> I am trying to export a report for SQL Reporting Services Report to
> Excel. Unfortunately the number of rows in my report are greater that
> the maximum allowable 65536 rows in an Excel sheet. Does anyone have a
> workaround that would allow for a SQL Reporting Services Report to be
> exported to Excel spread out over multiple sheets to make up for the
> 65536 row limitation?
>sql

export/data dump to flat file is too slow

I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.

I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.

So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?

Never mind. I was outputting it to a share, and that is the reason. When I modified it to do a bcp on the local drive on that server, it was flying like it is supposed to. I'll deal with copying it across the network later. Thanks.

Export to Mulitple Sheets in Excel

Hi,

I want to export data from SQL to Excel using Data Flow Task. But Excel file can only contains max. of 65,536 rows per sheet. How can I transfer data to multiple sheets if SQL table contains more that 650000 rows.

I am also looking for an answer to this. Very good question....

Export to Excel fails - ASPNET_WP.EXE consumes 60% RAM

I'm trying to export a report to Excel that contains 60,000 rows (but not
more than Excels 65,000 limit) and I get an "internal server error".
When I check the event log on the server it states that aspnet_wp.exe
terminated the report process because it had consumed more than 60% of the
servers availible RAM (1Gb total).
PDF and CSV work fine. Does anyone know anything about this'
--
Cheers
NickExporting to Excel is quite intensive on resources. The problem is that MS
is trying for very high fidelity on what the data looks like. For instance
if you have drill down it creates a spreadsheet with drilldown. For that
amount of data I suggest that you use CSV in ASCII format (the default is
Unicode). Excel doesn't recognize Unicode CSV and puts it all in one column.
With ASCII format Excel properly puts them in the correct columns. Another
tip, for your table in the report, click on each field of the detail and
make sure it has the name that you want on Export. The CSV export uses this
name. To get CSV in ASCII you need to use a jump to URL and include ASCII in
the deviceinfo (or something like that, doing this from memory from home).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nick Prudhoe" <NickPrudhoe@.discussions.microsoft.com> wrote in message
news:3F7F032F-9416-462B-89BC-F6C90E64FA5A@.microsoft.com...
> I'm trying to export a report to Excel that contains 60,000 rows (but not
> more than Excels 65,000 limit) and I get an "internal server error".
> When I check the event log on the server it states that aspnet_wp.exe
> terminated the report process because it had consumed more than 60% of the
> servers availible RAM (1Gb total).
> PDF and CSV work fine. Does anyone know anything about this'
> --
> Cheers
> Nick|||Thanks. I have already managed to get the same report to export to CSV. I'm
trying to prove reporting services out before my company goes down that
track. The performance on exports to Excel is disapointing, i would have
hoped it was better than PDF as it's an MS technology.
--
Cheers
Nick
"Bruce L-C [MVP]" wrote:
> Exporting to Excel is quite intensive on resources. The problem is that MS
> is trying for very high fidelity on what the data looks like. For instance
> if you have drill down it creates a spreadsheet with drilldown. For that
> amount of data I suggest that you use CSV in ASCII format (the default is
> Unicode). Excel doesn't recognize Unicode CSV and puts it all in one column.
> With ASCII format Excel properly puts them in the correct columns. Another
> tip, for your table in the report, click on each field of the detail and
> make sure it has the name that you want on Export. The CSV export uses this
> name. To get CSV in ASCII you need to use a jump to URL and include ASCII in
> the deviceinfo (or something like that, doing this from memory from home).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nick Prudhoe" <NickPrudhoe@.discussions.microsoft.com> wrote in message
> news:3F7F032F-9416-462B-89BC-F6C90E64FA5A@.microsoft.com...
> > I'm trying to export a report to Excel that contains 60,000 rows (but not
> > more than Excels 65,000 limit) and I get an "internal server error".
> >
> > When I check the event log on the server it states that aspnet_wp.exe
> > terminated the report process because it had consumed more than 60% of the
> > servers availible RAM (1Gb total).
> >
> > PDF and CSV work fine. Does anyone know anything about this'
> > --
> > Cheers
> > Nick
>
>|||Yeah...one would think...although I have seen a significant performance
improvement in service pack 2.
Nick Prudhoe wrote:
> Thanks. I have already managed to get the same report to export to CSV. I'm
> trying to prove reporting services out before my company goes down that
> track. The performance on exports to Excel is disapointing, i would have
> hoped it was better than PDF as it's an MS technology.
>

Export to excel > 65000 rows

Hi
We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.

Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?

ThanksI don't know of a way to do without a bit of scripting.

You could output all rows to a csv, then run vbscript that uses the excel object to create a new workbook and create multiple sheets (tabs - this is what you mean, right?), iterating through the csv.

Will people actually be looking at the data, row after row in excel? Why not use an access database? Excel wasn't designed for such large amounts of data, so using it for such a thing might not be a good idea in the first place.

Eric|||Why does it have to Excel?

Is someone going to look at the Data in Excel?

Who's gonna look at multiple tabs of 65,000 rows?

That's what the database is for

What are you ultimatley trying to do?

Export to Excel - more than 65536 rows

when exporting data to Excel..is there any way ..if data is more than 65536 rows.. it export the remaining data in next sheet

Out of curiosity, are you using a MicroSoft class to do this? Which one? And does it have a sheet number property?

|||

Hi,

From your description, it seems that you are failed to export data to excel which is more than 65536 records?

In Microsoft Excel 97 for Windows, Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003, text files that contain more than 65,536 rows cannot be opened in their entirety. You cannot open these files because these versions of Microsoft Excel are limited to 65,536 rows. If you open a file that contains more data than this, the text file is truncated at the row 65,536, and you receive the following error message:

File not loaded completely.

However, you can use a macro to open the file and automatically break the text into multiple worksheets

Versions of Excel earlier than Excel 97 have a limit of 16,384 rows.

Versions of Excel later than Excel 2003 have a limit of 1,048,576 rows.

For how to use a macro to open the file, see:

http://support.microsoft.com/kb/120596/en-us

Thanks.

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

export to excel

Hi,

In my report, I have 3 rows in the details section. I need to show all the 3 rows into a single line when it is exported to excel. Is this possible to do in reports, please help me.

I don't understand what you're trying to accomplish; if you can explain in more detail maybe I can give better guidance.

Each row in RDL will be exported to at least one row in Excel (it may span rows depending on the content of the cells and the position of other report items).|||

Hi,

I want to export every 3 lines in my report into 1 line in an excel sheet. Is there any way to do that.

|||Sorry, no.

Export to Excel

We are converting Crystal V10 reports to SSRS and have ran into an
issue when exporting a large dataset 104,000 rows. In Crystal when the
users export this same data it spreads it over multiple worksheets does
any have a solution in SSRS that gives me this same ability.Yes it is possible in SSRS, when you have pagination and export to excel,
then it will in multiple sheets
Amarnath
"smiksuek" wrote:
> We are converting Crystal V10 reports to SSRS and have ran into an
> issue when exporting a large dataset 104,000 rows. In Crystal when the
> users export this same data it spreads it over multiple worksheets does
> any have a solution in SSRS that gives me this same ability.
>

export to excel

Had anyone made an export to excel that has 33 rows?
33 rows in excel, together with header and all...
I'm asking that because in my case when export to excel has 33 rows, 33.
row is always missing!Do you have SP2 installed? I know it had some Excel export fixes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jo?ko ?ugar" <josko_bla@.netgen_rem0ve_this_and_bla.hr> wrote in message
news:d83nqc$3ll$1@.sunce.iskon.hr...
> Had anyone made an export to excel that has 33 rows?
> 33 rows in excel, together with header and all...
> I'm asking that because in my case when export to excel has 33 rows, 33.
> row is always missing!|||Bruce L-C [MVP] wrote:
> Do you have SP2 installed? I know it had some Excel export fixes.
>
I do now, but the problem is still there.|||Try adding a blank/empty textbox below your table or matrix. I've seen
cases like this where the last row of a report is missing when exported
to Excel...adding a small, empty textbox immediately below your table
or matrix seemed to fix the problem.|||kbr wrote:
> Try adding a blank/empty textbox below your table or matrix. I've seen
> cases like this where the last row of a report is missing when exported
> to Excel...adding a small, empty textbox immediately below your table
> or matrix seemed to fix the problem.
>
Thanks!