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 🙂 )
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.
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.
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)
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.
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.
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.
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.