Navision W1 40 Sql DB crash

Nagi

We are experiencing a crash in NAV W1 4.0 Sql after migrating large amounts of data. Because of a change in primary key in a customized table, we have to export old data and rebuild the index to match the new primary key before the data is imported again and the primary key is changed. After this job is done, NAV crashes when opening any company in the database (even one where the data has not been touched).

The rebuilding of data was initially done with temporary tables, but because of the 2GB limit on temporary tables in NAV we decided to export data with dataports. We have tried this in similar databases but with less data in said table and everything has worked fine. The problem only occurs when exporting/importing large quantities of data.

We are wondering if anybody else has experienced similar problems when exporting/importing large quantities of data. Any help on this issue would be appreciated, because we are at our wits end about this.

DenSter

How are you importing the data? If you're going straight into SQL Server, I'd say there's lower case characters in Code type fields, which NAV doesn't like.

If it's with dataports, it might be because it's just too much data and it can't page from memory. If it's a two step process, I'd consider splitting the process up, maybe put a COMMIT statement in there somewhere. Not after every single record, but every time it hits 500 records or something.

Nagi

Thank you for your suggestion. The export/import is done with dataports. First the data is exported to a text file, then deleted and then rebuildt as it is being imported back into the table. Then we merge the new primary key. We will try using a COMMIT to see if this helps. What we find puzzling is that the database doesn't crash during export/import, but it crashes afterwards, when we manually attempt opening a company in the database. Since all write transactions are finished at this point, and all changes have been commited, I would assume that any memory buffers are automatically flushed. :-k