[BUG Fixed 3.2.NEXT] execSQLtoSS with more then 256 columns

[ktoole]

I have tried to create a spreadsheet with more than 256 columns (750 columns allowed for Valence?). The spreadsheet gets created, but when I open it I get an error about the worksheet tag. This is what I found. A spreadsheet with less then 256 columns has this code in it:

</Styles>
<Worksheet ss:Name="Egg Production 030313"><Table ss:styleID="S21">
<Column ss:AutoFitWidth="1" ss:StyleID="char" ss:Width="40"/><Column ss:AutoFitWidth="1" ss:StyleID="val0" ss:Width="50"/><Column ss:AutoFitWidth="1" ss:StyleID="char" ss:Width="40"/><Column ss:AutoFitWidth="1" ss:StyleID="char" ss:Width="250"/><Column ss:AutoFitWidth="1" ss:StyleID="char" ss:Width="50"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="50"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num0" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="num2" ss:Width="70"/><Column ss:AutoFitWidth="1" ss:StyleID="percnt" ss:Width="70"/><Row><Cell ss:StyleID="Hchar"><Data ss:Type="String">Group Type</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Flock ID</Data></Cell><Cell ss:StyleID="Hchar"><Data ss:Type="String">Strain</Data></Cell><Cell ss:StyleID="Hchar"><Data ss:Type="String">Farm Name</Data></Cell><Cell ss:StyleID="Hchar"><Data ss:Type="String">Barn ID</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Hens Housed</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-03</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-03</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-03</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-04</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-04</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-04</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-05</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-05</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-05</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-06</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-06</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-06</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-07</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-07</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-07</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-08</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-08</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-08</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-09</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-09</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-09</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-10</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-10</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-10</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-11</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-11</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-11</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-12</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-12</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-12</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-13</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-13</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-13</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-14</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-14</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-14</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-15</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-15</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-15</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-16</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-16</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-16</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-17</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-17</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-17</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-18</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-18</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-18</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-19</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-19</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-19</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-20</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-20</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-20</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-21</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-21</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-21</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-22</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-22</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-22</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-23</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-23</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-23</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-24</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-24</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-24</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-25</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-25</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-25</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-26</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-26</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-26</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-27</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-27</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-27</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-28</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-28</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-28</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Egg Qty for �2013-03-29</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen for 2013-03-29</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay for 2013-03-29</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Report Total Egg Qty</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">Eggs/Hen Report Total</Data></Cell><Cell ss:StyleID="Hnum"><Data ss:Type="String">% Lay Report Total</Data></Cell></Row>
<Row>

This is a lot of columns. When I try to create one that has about 270 columns this is missing. This is what is there between the ending style tag and beginning row tag:

<Style ss:ID="TIN">
 <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
 <NumberFormat ss:Format="000\-00\-0000"/>
</Style>
</Styles>
<Row>

<Cell><Data ss:Type="String">E</Data></Cell>

The worksheet and column tags are not getting generated. Any thing that I might be doing that might be causing this? Both of the items above were generated from the same program.


I did a little test and added the xml code with the worksheet and column tags between the </Styles> and <Row> tags in the one that had problems and it opens just fine now. There are just no headings beyond the above defined column. Is this a bug in the execSQLtoSS program? I guess I thought it went out the 750 columns.

[BRandle]

So if you have some number less that 256 columns, the spreadsheet works, but at 270 columns the Column Headings don't generate, right?

The vvOut procedure can write errors - are there any errors generated that you would find in the Administration / Monitor / Errors ?

Are you using vvOut_toSS or vvOut_execSQLtoSS?

There is also a limit on the total size of the Headings - 32K. The ones above that worked are about 13K so you would run into problems at about double the size.

If this is SQL then I am not sure about going over 255 columns - that might be an sql limit.

[ktoole]

Valence1.jpgvalence2.jpg1) Correct
2) I don't get an error. The spreadsheet pops up, but will not load with the XML tags missing. I even get an indicator of '1' for success on the spreadsheet creation returning from vvOut_execSQLtoSS. When I go over to about 300 and some I do get an error and the spreadsheet is not returned. There the errors are attached below.
3) vvOut_execSQLtoSS
4) I run the process in debug and I am under the 32K, from what I can tell. I declared my variable that size and have dumped it out to a file to make sure that it is okay.
5) I'm not sure either about the 255 columns for SQL on the IBMi, but I thought with 7.1 it was larger than that.

[BRandle]

I was able to produce a corrupt spreadsheet when the Column headings plus the XML went over 32K. I updated the procedure for the next Valence Release so it will handle larger than 32K column headings.

[ktoole]

Thanks for your help. Is there a date set for the next update?

[richard.milone]

We are working on a new build right now and this fix is part of it. If QA checks go ok it could be out as early as later today.