Renumbering tables in NAV 2013 R2 – be careful!

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.

How?

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!

Say what?Watch Out!

When renumbering tables in NAV 2013 R2 with a wrong build, you will lose your data in that table! Without any warning.

Example:

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:

  1. Do not use Build No. lower than than 36310
    KB 2934572
    As 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.
  2. 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.
  3. Make sure that the Microsoft Dynamics NAV Server service account has been granted the “db owner” role in SQL Server.
  4. 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)
  5. 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).
  6. 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.
  7. 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 ;-).

4.00 avg. rating (83% score) - 3 votes

Permanent link to this article: https://www.waldo.be/2014/04/16/renumbering-tables-in-nav-2013-r2-be-careful/

7 comments

2 pings

Skip to comment form

    • Dave Machanick on April 16, 2014 at 1:09 am
    • Reply

    It seems to me they have made 2013 R2 less functional for developers. NAV has always been known for its flexibility for customizing. Now they are taking some of that away.
    What do you think?
    What % of NAV will be multi tenant 10 years from now? This appears to be the reason behind the changes.
    I will estimate between 1 and 10% – and more likely closer to 1% – if the feature still exists.

    • Stephen on April 16, 2014 at 8:19 am
    • Reply

    I am glad other people are finding these issues too. I personally wish i could turn off all this multitenancy and get back the stable product we had before. I would import objects manually into each database rather than have these types of issues.

    • Disappointed NAV Developer on April 16, 2014 at 8:44 am
    • Reply

    I’d vote for 0 to 1%…(and close to one only if you count all of the teneants as separate installations 😉
    Really I dont like that direction…

  1. Hi Dave,

    the question is more like “Will NAV exist 10 years from now?”… that’s quite depressing, IMO. With the current state of affairs I guess we have 2 years left. And I am more or less convinced that the SaaS approach is the wrong one for NAV. Not with the foundation and technlogy we have now.

    with best regards

    Jens

      • Tim on April 13, 2017 at 6:12 am

      OK, Jens. 3 more days left. Will NAV still be available? What is your guess?

  2. Let’s not exaggerate .. . We’re talking about renumbering in combination with keeping data here.. . That’s it.. .
    Sure, it has to do with supporting the MT environment, but then again .. I won’t miss this flexibility too much ;-). Thanks to PowerShell .. NAV has become far more flexible on other parts .. so is NAV really less flexible?

    I know of quite some (big) implementations already on MT. It’s a new business, for sure. And people that are doing On Premise business, have difficulties to see what benefit it has for them. Also in our company, sure.

    In my opinion, On premise will exist for long.. . But who am I .. I’m just a NAV developer ;-).

  3. Oh don’t get me started on anything RTC… the recent MT-related problems and new “paradigms” (released 6 months after the product is out, mind) are only the tip of the iceberg. I mean what I say. At least here in Germany, NAV is not a viable business proposition. And this means no sales. But who am I… I’m just a NAV developer, too. 😉

  1. […] Continue reading » […]

  2. […] Bron : Waldo’s Blog Lees meer… […]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.