Object ID’s .. it’s still a hassle .. but something we’re all used to work with. But sometimes, ID’s of objects need to be renumbered. You have to move a piece of functionality to a new number band, or you want to incorporate a piece of an app into another database, which would result in overlapping object id’s. No matter what situation .. every developer sometimes need to renumber objects.
Well, it’s not really the topic of this blog .. but now that we’re talking about it: I like to use the Mergetool (mergetool.com) for this. It never failed on me, and seems to take all internal references into account! So I can strongly advise you to use the renumber-part of mergetool.
So what is the topic of this blog?
Well, since NAV 2013 R2, you really have to be careful when you start renumbering tables! You could potentially loose data!
When renumbering tables in NAV 2013 R2 with a wrong build, you will lose your data in that table! Without any warning.
Suppose we have a custom table 50001, called “TestRenumber” with data:
Let’s try to renumber it to 50002:
Compile the table
When running the table, the data is gone:
In a way, Microsoft has blogged about it in their blog about The New Table Synchronization Paradigm in Microsoft Dynamics NAV 2013 R2. I strongly recommend to read this blog, so that you understand this synchronization that is going on in R2. In the blog, Microsoft has a few developer best practices, which I would like to repeat here, as I fully support them:
- Do not use Build No. lower than than 36310
KB 2934572As a partner, you take this as the “RTM Build No.” starting point for NAV 2013 R2 and deploy this platform hotfix in the future projects while you also convert existing installations.
NOTE: As per common best practice, we recommend that you download / request / test and deploy the latest platform hotfix for Microsoft Dynamics NAV 2013 R2. This will contain correction for minor issues not directly or just slightly related to synchronization scenarios.
- Never-ever change “Prevent data loss from table changes” to “No”.
This have been noticed as one of the major source of potential data loss and missing synchronization for NAV 2013 R2 databases.
- Make sure that the Microsoft Dynamics NAV Server service account has been granted the “db owner” role in SQL Server.
- Increment the SQL Server Command Timeout parameter in the Microsoft Dynamics NAV Server configuration file that you use in development to a very high value (such as 10:00:00)
- For large Microsoft Dynamics NAV objects OR a high number of table modifications, do NOT use a Microsoft Dynamics NAV client action to prompt for synchronization but it is warmly preferable to use the Sync-NAVTenant Windows PowerShell cmdlet. (This is a typical scenario related to upgrades).
- For big batch of FOB files that are making a high number of table modifications, be sure to have this tested on a safe staging environment and import, where possible, the Table Objects in smaller chunks and synchronize them after importing every single chunk of Microsoft Dynamics NAV objects.
- For important changes in several table structures, such as when upgrading from previous version, it would be good to run a SQL Server Profiler trace after prompting for synchronization to check what is running on the SQL Server side and keep the synchronization monitored until it ends.
Ok, I read the blog, but didn’t find anything about problems with renaming/renumbering
I know .. that’s why I’m telling you now. In a way, it’s a result of the same way of working, I guess.
At this moment, I’m not aware of any decent build that can do a table renumbering without any issues. In the most recent build that I could get (36366), I’m not losing data anymore, but when renumbering a table, the DEV environment crashes. I was told that renumbering a table is not going to be allowed anymore and would therefore throw an error when you try to do so.. .
So, are there any best practices in how to renumber tables and preserving data then?
Well, it was a long search. But there seems to be a KB article about it. It describes how to renumber objects, and in case you want to preserve data, it describes what you should do:
|Additional steps Only if updating an existing site, and your Customer already had data in the new tables. 7. After the new text file is created, but before it is imported, rename each of your old tables (just put the word ‘Old’ in front of each current name) in the database. 8. Remove all of the other Objects (forms, reports, codeunits and dataports) which were created in the old system (do NOT remove base application Objects). Then import your text file and recompile all Objects. 9. Create a process (normally a report) to transfer the data from each old table to its corresponding new table. The TRANSFERFIELDS function can be used to transfer the data from the old record to the new record. Once the data has been transferred and verified, remove the old data, the old tables, and the conversion process.|
Is this a big problem?
Well, I don’t want to make too much of a fuss about it. In a way, renumbering is being done – relatively – a lot, I believe .. but the combination “renumbering and preserving data” .. I guess not. It’s just a coincidence that I bumped into it, as I had a situation like it: developed it without thinking too much of number bands .. and later on came into the situation that I HAD to move all of it. The workaround that is described above obviously works like a charm – just hope you don’t have to do this for too many tables ;-).