Database to excel 200 000 records, timing out
Posted: Mon Mar 27, 2017 9:59 pm
Hello,
I'm trying to create excel from database output.
Rowset has more than 200K rows, project is runing and at some point fails. Here is log:
3/27/17 9:18:26 PM INFO Executing project 'Oracle report for client'
3/27/17 9:18:26 PM INFO Project location: \test\Oracle report for client.xml
3/27/17 9:18:26 PM INFO Executing module 'Main'
3/27/17 9:18:29 PM INFO Executing task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:31 PM INFO Executing sub-task 'query'
3/27/17 9:18:31 PM INFO Executing statement - SELECT * FROM PWR_UNBLIND_DU_LIST_TABLE_VIEW
3/27/17 9:18:31 PM INFO Query completed successfully and the rowset 'data' was created
3/27/17 9:18:31 PM INFO Finished sub-task 'query'
3/27/17 9:18:31 PM INFO Finished task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:35 PM INFO Executing task 'writeExcel 2.0 (Convert to Excel)'
3/27/17 9:18:35 PM WARN Overwriting the output file 'Global Production Processes\client\disp.xls'
3/27/17 9:20:30 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 2 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.
3/27/17 9:34:01 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 3 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.
Hangs ... and then fails with error:
Project 'Oracle report for client' is defined as not thread safe and an instance with job number '1000000623844' is already running.
Please wait for this job to finish and then try again.
Then I have to reboot a server. because GoAnywhere services hangs and not responding.
<writeExcel label="Convert to Excel" inputRowSetVariable="${data}" outputFile="\Global Production Processes\client\disp.xls"
whenFileExists="overwrite"
excelFormat="excel2007"
whenSheetFull="rename sheet" includeHeadings="true" outputFileVariable="excelFile" version="2.0" logLevel="debug">
<data trim="right" dateFormat="MM/dd/yyyy" timestampFormat="MM/dd/yyyy" />
<headings backgroundColor="Gold" />
</writeExcel>
I'm trying to create excel from database output.
Rowset has more than 200K rows, project is runing and at some point fails. Here is log:
3/27/17 9:18:26 PM INFO Executing project 'Oracle report for client'
3/27/17 9:18:26 PM INFO Project location: \test\Oracle report for client.xml
3/27/17 9:18:26 PM INFO Executing module 'Main'
3/27/17 9:18:29 PM INFO Executing task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:31 PM INFO Executing sub-task 'query'
3/27/17 9:18:31 PM INFO Executing statement - SELECT * FROM PWR_UNBLIND_DU_LIST_TABLE_VIEW
3/27/17 9:18:31 PM INFO Query completed successfully and the rowset 'data' was created
3/27/17 9:18:31 PM INFO Finished sub-task 'query'
3/27/17 9:18:31 PM INFO Finished task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:35 PM INFO Executing task 'writeExcel 2.0 (Convert to Excel)'
3/27/17 9:18:35 PM WARN Overwriting the output file 'Global Production Processes\client\disp.xls'
3/27/17 9:20:30 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 2 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.
3/27/17 9:34:01 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 3 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.
Hangs ... and then fails with error:
Project 'Oracle report for client' is defined as not thread safe and an instance with job number '1000000623844' is already running.
Please wait for this job to finish and then try again.
Then I have to reboot a server. because GoAnywhere services hangs and not responding.
<writeExcel label="Convert to Excel" inputRowSetVariable="${data}" outputFile="\Global Production Processes\client\disp.xls"
whenFileExists="overwrite"
excelFormat="excel2007"
whenSheetFull="rename sheet" includeHeadings="true" outputFileVariable="excelFile" version="2.0" logLevel="debug">
<data trim="right" dateFormat="MM/dd/yyyy" timestampFormat="MM/dd/yyyy" />
<headings backgroundColor="Gold" />
</writeExcel>