Is there a limit on the number of records you can have in a temporary table?
The reason for asking is that we have a routine that runs through a table, stores records in a temp table, then reads them back. If we run this on a subset of the data it works fine, however, if we run it on the entire source table (and this creates >1 milliion temp records) we get the error
The operating system returned the error (131):
An attempt was made to move the file pointer before the beginning of the file.
Any ‘pointers’ to the solution would be great! 
Thanks
Justin
Edit: it’s Navision 4.01 exe, 2.6 objects, SQL 2000 Enterprise edition, Win2k OS!
Do you have enough memory? Temporary tables run in client, so that 1 million records are stored in client not in server. I don’t think temporary tables were designed to hold that much information.
I would also think its RAM. In my first Financials training class, they said that there is limit on the number of records depending on RAM, but didn’t say what the limit or ratio was.
You may be able to increase the size of your swap file, but that wont be ideal.
In any case, placing a million records in ram is goign to be slow, So maybe its better to rethink the design.
This is a limitation of the Navision temp file. It has a max size of 2GB (SQL and Native), when you hit that limit you receive your error message. I have received this error message several times over the last few months and I finally received an answer back from Microsoft that this limit doesn’t effect very many clients, so they have no plans on fixing it. I receive the error any time we have a general journal over approx. 400,000 records.
MPH
I don’t think it can be considered as a bug. Temporary tables were designed to hold temporary data, during posts, etc.
Client / NAS performance on server will decrease even with 1GB temporary table.
Thanks for your help folks - we’ve recoded it to 200,000 records at a time and this is OK.
Cheers
Justin