About Malcolm Burtt

Malcolm is a propeller head who has embraced the Dynamics AX platform for more than 16 years. Malcolm loves pushing the platform to its limits; finding the right solution and maximising its performance. When not messing about in AX, Malcolm loves sailing and reading...but his two boys generally have other ideas about what he should be doing with his time which is okay given that both boys are fun to be with.

Extensions are the future – time for a gatekeeper role

The message should have gotten through load and clear by now, customisation of Dynamics 365 for Operations (AX7) should, wherever possible, be done using Extensions. Your development team should have seen this being hammered home by Microsoft and the D365 community over the past months and especially since Microsoft told us that the Application Suite will be sealed by spring 2018. So how do you stop your development team from falling back on old habits by using their tried and test over layering techniques?

Continue reading

Comparing AX 2012 hotfix model lists

When Microsoft started releasing hotfixes for the Dynamics AX 2012 R3 release they made our lives a little harder. If you had just one or two hotfixes load it wasn’t too bad but as eventually you find yourself loading a hotfix that has hundreds of dependant hotfixes and suddenly your model store has a list of models a mile long in it. So, when you’re promoting code from your Development system through to Test and Production you need to be sure that you have all the right hotfixes loaded and with a long list of models in each model store, you can easily make a mistake and miss something.

Continue reading

Merging MorphX Version Control data

A while back I wrote about moving version control data between AX development systems but recently I found that, although I needed to move the version control data from one system to another, we had already started development in the new system and there were objects already checked into version control there.

Rather than abandon all the useful historic version data or the new version data, I decided to look at whether I could merge the version data from the two systems without causing myself too much pain. It quickly became obvious that it is actually a pretty easy thing to do. For the sake of my own sanity I did all of this work with both AOS services shut down and no objects checked out in the target system (although this last condition isn’t a requirement).

In my case the system containing the source version data was no longer being developed in, so the version control lock data was irrelevant; I only needed to move the revision and item data. The SysVersionControlMorphxItemTable table has unique keys on the ItemPath and RecId fields so I needed a strategy to ensure that there are no collisions of keys on either field. In the end, I worked out that because it was likely that some of the same items had been modified in both systems, the simplest way to populate the SysVersionControlMorphXItemTable data was to fill the SysVersionControlMorphXRevisionTable first and then write a little bit of x++ code that would create any missing SysVersionControlMorphXItemTable rows based on ItemPath values in the SysVersionControlMorphXRevisionTable data that were missing in the SysVersionControlMorphXItemTable data.

So that just left the SysVersionControlMorphXRevisionTable. In this case I had a number of problems to overcome:-

  1. The RecId field is a unique key and is likely to be duplicated in both systems
  2. The ItemPath & Version fields form a unique composite key and these values are likely to be duplicated in both systems

Preventing duplicate RecId values

Let’s deal with the RecId key first. What we need to do here is to increase the all of the values of the RecId field in the target system so that when we insert the records from our source system, they will simply slide into the table underneath the existing RecId values. To do this I selected, I ran the following SQL script from SQL Server Management Studio against the source systems’s application database.

SELECT MAX(RecId) AS maxSourceRecId From SysVersionControlMorphXRevisionTable

This gives us a maxSourceRecId value.

Next I needed to know the lowest recId value (we’ll call this minTargetRecId) from the SysVersionControlMorphXRevisionTable in the target system which I got using the following SQL Script.

SELECT MIN(RecId) AS minTargetRecId FROM SysVersionControlMorphXRevisionTable

In my case, I have more revisions in my source system than in my target system and this seems like the most likely scenario anyone would come up against so the following is based on that assumption.

I now need to find the value that needs to be added to the target system’s recIds, which you calculate as follows:-

valueToAdd = (maxSourceRecId – minTargetRecId) + 1

…and then I just need to run the following SQL to update  the recIds in the target database.

UPDATE SysVersionControlMorphXRevisionTable
SET RecId = RecId + valueToAdd

We also need to ensure that AX realises that the recId values for this table have been increased so that it issues recId values for new rows that are unique, which we can do with the following SQL…

UPDATE SystemSequences 
SET NEXTVAL = (SELECT MAX(RecId) + 1 FROM SYSVERSIONCONTROLMORPHXREVISIONTABLE) WHERE tabid = 2543

Preventing duplicate revision numbers

Next we need to ensure that no two revisions for the same ItemPath have the same value.

I decided to take a really simple approach here largely because my two application databases were on different SQL Servers and I didn’t want to have to deal with cross server SQL queries.

In my case, all of the revisions that have occurred in the target system happened after the last check in on the source system, so I want all of the revisions in the target system to have a higher value than those in the source system. Rather than worry about trying to ensure that the version numbers for each ItemPath value were contiguous I took the pragmatic view that I would just add the same value to all Version number values in the target system, so I needed to know what the highest Version number value was in the source system…

SELECT max(VERSION) AS HighestVersion FROM SysVersionControlMorphXRevisionTable

and then we can use this value to increase the version numbers on the target system…

UPDATE SysVersionControlMorphXRevisionTable
SET VERSION = VERSION + HighestVersion

By happy coincidence, my value for HighestVersion was 49  which meant that all of my version numbers from the newer modifications in the target system would all start at 50…a nice round number that will be easy to identify later if I need to.

Loading the source system data

Now we should be able to extract the data from the source system and load it directly into the target system. We’ll do this in the same was as I described in my original post

bcp dbo.SysVersionControlMorphxRevisionTable out vcsrev.dat -T -d sourceDatabase -S sourceDBServer -N
 
bcp dbo.SysVersionControlMorphxRevisionTable in vcsrev.dat -T -d targetDatabase -S targetDBServer -N

Creating missing item data

Our final task is to create the missing SysVersionControlMorphXItemTable data that I described above, which we can do with a simple x++ Job.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
static void initVCSItemsFromRevision(Args _args)
{
    SysVersionControlMorphXRevisionTable    rev;
    SysVersionControlMorphXItemTable        item, item1;
    int                                     recs;
 
    ttsBegin;
    while select itemPath from rev
        group by itemPath
        notexists join item
        where item.ItemPath == rev.ItemPath
    {
 
        item1.clear();
        item1.ItemPath = rev.ItemPath;
        item1.insert();
        recs++;
    }
    ttsCommit;
 
    flush SysVersionControlMorphXItemTable;
    info(strFmt("Added %1 items", recs));
}

Once you’ve completed all of these steps you should have successfully merged the version control data from the two systems and your target system will now hold the combined version history.