Error 131 when using Excel Buffer in NAV4

sre · 2009-09-28

Hi everyone,

I've created a marketing tool in Navision 4 SP3 with the attempt to generate an Excel file which can be used in other tools. The result of a selection within this marketing tool is kept in a regular database table. When the user start the export function, this information is send to the Excel buffer and exported to Excel using the Navision standard functions.

My problem is that "Error 131 - An attempt was made to move file pointer before beginning of file" occurs when the number of records exceeds a certain limit. I've already searched the forum and found similar problems, mainly due to the limitation of 2 GB for the temporary files in Navision, but no solution.

I've set the Excel Buffer table to "permanently" to make my export work. In that case, the completely filled Excel Buffer table has a size of only a little more than 160 MB based on 32.000 records, so I can't imagine why a temporary file should exceed 2 GB.

I'm a little bit lost here but would like to avoid marking the Excel Buffer permanently. Therefore all answers and suggestions are welcome! :-)

Thanks in advance!
Sascha

kine · 2009-09-28

That the data in table have some size, doesn't mean that the excel will not have over 2GB. Just look at empty excel file size...;-)

sre · 2009-09-29

Thanks for your reply, Kamil.

You're right that the size of the table doesn't directly refer to the size of the Excel file. But the final Excle file (after exporting the Excel Buffer table with more than 160 MB) is less than 18 MB. I guess that's due to the large number of records in the buffer table as it contains one record per Excel cell.

Besides the fact that I still can't imagine that the limit of 2 GB can be reached by this export, I don't think that the Navision limitation of 2 GB would apply to the Excel file as this would be a (temporary) file created by the Excel process, not Navision.

Did anyone ever hear of other limitations like the number of records in a temporary table which might explain this error message in my case? I'm looking for ways to create a workaround but currently I don't know around what I have to work around. :?

Thanks in advance for your support ...
Sascha

kine · 2009-09-29

1) Are you using Report for this functionality?
2) If 1 is "yes", are you using some sections to print some data?
3) If 2 is "no", did you set the property "Processing only" on the report to "True"?

If you are using report and you didn't remove empty sections for your dataitems and you didn't changed the report to "processing only", the report is "printing" to many pages. These pages are generated into temp file first, and size of this file may be the problem.

sre · 2009-09-29

kine wrote:

1) Are you using Report for this functionality?

Yes

kine wrote:

2) If 1 is "yes", are you using some sections to print some data?

No

kine wrote:

3) If 2 is "no", did you set the property "Processing only" on the report to "True"?

Yes

The basic function of that report is that it first transfers the data from my result table to the Navision table Excel Buffer. After that the Excel function is used to transfer this data to Excel. The error already occurs in the first part of the report (where Excel isn't involved yet) when the data from the result table is transfered to the temporary table Excel Buffer.

Is there a way to verify the size of the Navision temp file (to be honest I've got no idea where this is supposed to be, I guess somewhere in local settings\temp ...)? I'm still not willing to believe that this file exceeds 2 GB in my sitatuation as the table size (when I turn the temporary property off) is only 160 MB.

Thanks again in advance for all contributions :-)

matttrax · 2009-09-29

You could check the temp files. They are in the same place as the zup file. You'll recognize them by their weird file names. Every now and then I'll see a user with as many as 10 of these. We always delete them without any problems, but I'm unsure of what they are used for. Maybe someone can answer that one too

sre · 2009-09-29

Thanks matttrax ... like Aerosmith said .... "It's amazing". :-)

I couldn't believe it but indeed the problem is caused by the 2 GB limit of the temporary file! I've just watched the temp folder while running my report and the size of the temp file which is created then goes up to 2 GB. I would be quite curious to know in what kind of structure the data is stored there (as pointed before, in a "real" table only 160 MB are required including all keys).

Nevertheless, I guess I've got to accept that as this obviously is the reason for my problem, too.
I guess I'll use a regular table and take care of cleaning up this "temp" table myself. ](*,)

Thanks for your help kine and matttrax!

kine · 2009-09-29

May be there is some loop in your code, which is creating and filling new and new copies of the temp table? If there is only one global variable, I think it is not problem, but to be sure...

sre · 2009-09-29

The report iterates on one data item which is based on my original result table. For every record the write function is called which inserts the record into the temporary Excel Buffer table. To me it appears correct which is supported by the fact that it works when the Excel Buffer is not defined as temporary (because then all records are created correctly - with the right content and quantity).

I guess having the table structure in a plain file creates some kind of overhead ... I just couldn't imagine it was that much :-)

Thanks again.

Error 131 when using Excel Buffer in NAV4

Answers

Categories