Moving objects between layers

Today I am working at a customer site and today’s task is to move all objects from the USR layer to the VAR layer. (Don’t ask why but due to licensing we could not work in the VAR layer)
There is already data present, so we cannot just move the objects without further attention.          

There are some solutions to the problem that I’ve stumbled upon on the web (f.e. http://www.axaptapedia.com/Move_DB_objects_to_another_layer ) but these did not do the job entirely.
Some problems that you may encounter and need to be addressed are:          

  • System fields that are in the range >= 60000 need to be taken into account
  • Long table names will be shortened by the SQL server backend to 30 chars (25 name + the 5 chars long Id of the table) So when changing the Id of a table that has a long name, you also need to rename the table on SQL server to contain the newly obtained ID

As always, there are several ways to travel to Rome but this is what I did. I have created a wizard that can be run before moving the table to collect dictionary data. Then you can move the object to another layer and rerun the wizard again to do the actions needed after moving the object.          

In general, this is what we need to do:          

  • Disable the synchronization code to prevent the system from executing a synchronization automatically
  • Run the wizard to gather the current dictionary data
  • Export the table and delete it from the USR layer
  • Import the table in the correct layer
  • Run the wizard again to gather the new dictionary data and update the SqlDictionary
  • Re-enable the synchronization code and perform synchronization

So let’s start! (By the way: Don’t mind the to-do’s, labels, best practices) because it is working, but will need some polishing 🙂 )         

Disable synchronization  

First we make an adjustment in the Application class, method DBSynchronize. We just make sure here the code is not executed. This is actually some stupid code and should be replaced with some kind of parameter to be able to enable / disable it.         

DBSynchronize modification

 

Now that the system will not synchronize until we make the change undone, we can start messing things up 🙂         

 Run the wizard before layer move        

 Before moving the table to another layer, we will gather all the current id’s to be able to lookup some stuff after the move to the other layer.  In the first step you need to specify the layers used and fill in the Pre-layer move option.      

Move Parameters

 

Then depending on pre/post layer move, the wizard shows a tab where you can enter tables to process when pre-layer move, or shows the results directly as in the next figure. (After the move, he uses the tables he finds in the log table)     

Wizard results

 

Then the wizard gathers everything and shows the data. In this case the new ids are empty because we are processing before the move. Just click next and finish the wizard.       

Exporting the objects  

Now that we have collected the ids we need to remember, we can export the tables (without id’s and labels) and delete the objects.       

Importing the objects  

After deleting the objects in the USR layer, we can import them in the VAR layer. This is a normal import so nothing special here.          

Run the wizard post move and update the SqlDictionary  

Now that the table is in the correct layer with the new ids generated, we need to run the wizard again to run some scripts on the SQLDictionary system table. The SQLDictionary table contains a representation of what Dynamics Ax thinks is present in the SQL database. So this table is used by the kernel to determine whether a alter table or create table statement is needed at the database backend side. 

To make sure the kernel will not try to create tables that already exist in the database with that name, we have to make sure the old id’s are replaced by the new ones. Same thing for field id’s…       

So we run the wizard again and specify the post-layer move option.       

Wizard parameters after moving table

 

Then we will not be prompted to specify tables but the wizard will process the same tables / fields as in the pre-move run of the wizard.
We get to see what will be adjusted in the SQLDictionary table in the next step.       

Results after moving the table

 

 So we have the old id’s to look for in the SQlDictionary table and the new id’s to replace them. So let’s click next and let the wizard do the processing.
This will result in the dictionary to contain the right id’s and prevent the table to be dropped and recreated by SQL server and thus keeping all the data.       

SQLDictionary results

 

Also note that the wizard takes care of long names that are shortened by SQL. The id is appended to the shortened table name so there is also a renaming needed at the backend side.      

So there you have it! Please feel free to comment on this.      

Caution: Doing stuff like this isn’t without any danger. The synchronization process does tons of stuff we (as non kernel developers) don’t know about so it might be possible there are some special cases in which this could be going wrong.
I try to process the tables in small bunches and take backups in the process so that I don’t have to run it for everything at once and en up halfway with some broken link in the SQLDictionary.      

Axaptapedia link…
http://www.axaptapedia.com/Moving_Table_between_layers
 

Download…
http://www.ksaelen.be/DynamicsAxXpo/SharedProject_KeSae_TableLayerMove.xpo

10 thoughts on “Moving objects between layers

  1. Awesome, nice work!

    What I do (did) is create a duplicate table in SQL and copy all data to it, and then after sychronization (when the tabel or fields are dropped and recreated, copy the data back to the original table.

    But this is better :-).

  2. The SQL table copy solution is indeed something that is also a very good way of doing this.

    A while ago I thought about it and started to create some C# tool that used this sql table copy style and logs on to Dynamics and does some stuff but I ran into the well known problem that I could not log on to dynamics twice in the same program instance (log on in one layer, do your thing, log off and then log on into the destination layer)

  3. Wow great Solution for this big problem!!
    I was looking a long time for a final solution.

    Could you send it to me please?

    Best regards

    Azrael

  4. Nice solutions.

    Can you send me the code?

    This would be very nice?

  5. Congratulation for this solution.
    It seems to be very well.
    I will Try It !
    Thanks for the post and the download link !

  6. Getting the following error when importing the xpo.
    Any ideas?

    The method override has an incorrect number of parameters. \Classes\NVMPTableLayerMoveWizard\skipTab
    1
    skipTab
    Err:86

  7. OK, sorted that issue, looks like it extends a lower class that has changed a little in latest CU but now if I run the wizard I get this despite the fact I have full admin permissions.

    You are not authorised to access table ‘@HEL287’ (NVMPSqlDictionaryIdChanges). Contact your system administrator.

Leave a Reply

Your email address will not be published. Required fields are marked *