Axaptapedia is now maintained by AgileCadence For more information please click here.
Move DB objects to another layer
From Axaptapedia
... without losing data
If you want to transfer DB objects e.g. from USR to CUS layer, the database fields are deleted and recreated, data is lost. To prevent that, we can:
- Export data, move the object and import data
- Export the objects with IDs (WITHOUT IDs, if your Microsoft Dynamics AX version is 5.0)
- Fake IDs for the target layer
I created jobs to "correct" the IDs:
The first job LayerMove_USRCUS has to be executed before Export of USR-Layer. It fakes the tableIds and fieldIds. The second job AfterLayerMove has to be executed after the Import into the CUS-Layer. It will correct the tableIds again (necessary).
So the sequence is:
- Backup DB and Application
- Job LayerMove_USRCUS
- export USR
- delete USR Layer
- import CUS
- Job AfterLayerMove
- reimport CUS (this step is not necessary in Microsoft Dynamics AX 2009)
- synchronize database
<xpp>
static void LayerMove_USRCUS(Args _args)
{
//if other layers needed, only the macro values need to be changed
#define.layerText("USR to CUS") // from - to layer
#define.minUSRid(50001) // minimum id of the from-layer
#define.maxUSRid(59999) // maximum id of the from-layer
#define.minCUSid(40001) // minimum id of the to-layer
#define.maxCUSid(49999) // maximum id of the to-layer
#define.defCUSid(40001) // default id to start with, mostly = #minCUSid
SysLastValue sysLastValue;
SQLDictionary sqlTables;
SQLDictionary sqlDictionary, sqlDictionary2;
TableId tableId, maxCUStableId;
int currentId, recentFieldId;
str fieldList;
boolean yesToAll = false, moveFields;
int answer;
;
setPrefix("Transfer tables and fields from " + #layerText);
//look for the highest table id in the destination layer
select firstOnly sqlDictionary
order by tabId desc
where sqlDictionary.fieldId == 0 &&
sqlDictionary.tabId >= #minCUSid &&
sqlDictionary.tabId <= #maxCUSid;
maxCUStableId = sqlDictionary.tabId ? sqlDictionary.tabId : #defCUSid - 1;
//loop thru all tables
while select sqlTables
order by name
where sqlTables.fieldId == 0
{
tableId = sqlTables.tabId;
//if the table or some fields are located in the source layer...
moveFields = (tableId >= #minUSRid && tableId <= #maxUSRid);
if (!moveFields)
{
select firstOnly sqlDictionary
order by fieldId desc
where sqlDictionary.tabId == tableId &&
sqlDictionary.fieldId >= #minUSRid &&
sqlDictionary.fieldId <= #maxUSRid;
if (sqlDictionary) moveFields = true;
}
if (moveFields)
{
setPrefix(strFmt("Table %1 (Id %2)", sqlTables.name, sqlTables.tabId));
fieldList = "";
while select sqlDictionary
order by fieldId asc
where sqlDictionary.TabId == tableId &&
sqlDictionary.array == 1 &&
sqlDictionary.fieldId >= #minUSRid &&
sqlDictionary.fieldId <= #maxUSRid
{
fieldList += (fieldList ? ", " : "") + sqlDictionary.name;
}
//ask for permission to change
if (!yesToAll)
{
answer = Box::yesYesAllNoCancel("ok to move" +
((tableId >= #minUSRid && tableId <= #maxUSRid) ?
" table " + sqlTables.name : "") +
(fieldList ? " fields " + fieldList : "") +
" from " + #layerText + "?", DialogButton::No);
yesToAll = (answer == DialogButton::YesToAll);
if (answer == DialogButton::Cancel)
break;
}
if (yesToAll || answer == DialogButton::Yes)
{
ttsbegin;
//if table is located in the source layer...
if (tableId >= #minUSRid && tableId <= #maxUSRid)
{ // move table to CUS: look if already exist in lower layer
select firstOnly forUpdate sqlDictionary
where sqlDictionary.tabId == sqlTables.tabId &&
sqlDictionary.fieldId == 0;
select firstOnly sqlDictionary2
order by tabId desc
where sqlDictionary2.name == sqlTables.name &&
sqlDictionary2.tabId != sqlTables.tabId &&
sqlDictionary2.fieldId == 0;
if (sqlDictionary2)
{
tableId = sqlDictionary2.tabId;
sqlDictionary.delete();
//delete also dataAreaId, recId
delete_from sqlDictionary
where sqlDictionary.TabId == sqlTables.tabId &&
sqlDictionary.fieldId > 0 &&
(sqlDictionary.fieldId < #minUSRid ||
sqlDictionary.fieldId > #maxUSRid);
}
else
{
maxCUStableId++;
tableId = maxCUStableId;
sqlDictionary.tabId = tableId;
sqlDictionary.update();
//change dataAreaId, recId and some other fields
while select forUpdate sqlDictionary
order by fieldId asc, array asc
where sqlDictionary.TabId == sqlTables.tabId &&
sqlDictionary.fieldId > 0 &&
(sqlDictionary.fieldId < #minUSRid ||
sqlDictionary.fieldId > #maxUSRid)
{
sqlDictionary.tabId = tableId;
sqlDictionary.update();
}
}
delete_from sysLastValue
where sysLastValue.elementName == strFmt("T:%1", sqlTables.tabId);
info(strFmt("table id changed from %1 to %2", sqlTables.tabId, tableId));
}
//look for the highest field id of that table in destination layer
select firstOnly sqlDictionary
order by fieldId desc
where sqlDictionary.tabId == sqlTables.tabId &&
sqlDictionary.fieldId >= #minCUSid &&
sqlDictionary.fieldId <= #maxCUSid;
currentId = sqlDictionary.FieldId ? sqlDictionary.FieldId : #defCUSid - 1;
recentFieldId = 0;
//search for all fields in the source layer
while select forUpdate sqlDictionary
order by fieldId asc, array asc
where sqlDictionary.TabId == sqlTables.tabId &&
sqlDictionary.fieldId >= #minUSRid &&
sqlDictionary.fieldId <= #maxUSRid
{
select forUpdate sqlDictionary2
order by fieldId desc
where sqlDictionary2.TabId == tableId &&
sqlDictionary2.fieldId != sqlDictionary.fieldId &&
sqlDictionary2.array == sqlDictionary.array &&
sqlDictionary2.name == sqlDictionary.name;
if (sqlDictionary2)
{
if (sqlDictionary2.fieldId >= #minCUSid &&
sqlDictionary2.fieldId <= #maxCUSid)
{
currentId = sqlDictionary2.fieldId;
sqlDictionary2.delete();
}
}
else
{
// array fields have the same fieldId
if (recentFieldId != sqlDictionary.fieldId)
{
currentId++;
recentFieldId = sqlDictionary.fieldId;
}
}
info(strFmt("fieldName: %1, id changed from %2 to %3",
sqlDictionary.name,
sqlDictionary.fieldId, currentId));
sqlDictionary.tabId = tableId;
sqlDictionary.fieldId = currentId;
sqlDictionary.update();
}
ttscommit;
}
}
}
setPrefix("Transfer");
info("Transfer completed");
}
</xpp>
<xpp>
static void AfterLayerMove(Args _args)
{
//if other layer needed, only the macro values need to be changed
#define.layerText("CUS") // from - to layer
#define.minCUSid(40001) // minimum id
#define.maxCUSid(49999) // maximum id
SQLDictionary sqlTables;
SQLDictionary sqlDictionary;
Dictionary dictionary = new Dictionary();
TableId tableId;
;
setPrefix("Checking tables of layer " + #layerText);
//loop thru all tables
while select sqlTables
where sqlTables.fieldId == 0 &&
sqlTables.tabId >= #minCUSid &&
sqlTables.tabId <= #maxCUSid
{
tableId = dictionary.tableName2Id(sqlTables.name);
if (tableId != sqlTables.tabId && tableId && sqlTables.tabId)
{
ttsbegin;
while select forUpdate sqlDictionary
where sqlDictionary.tabId == sqlTables.tabid
{
sqlDictionary.tabId = tableId;
sqlDictionary.update();
}
info(strFmt("table %1 id changed from %2 to %3",
sqlTables.name, sqlTables.tabId, tableId));
ttscommit;
}
}
setPrefix("Transfer");
info("Transfer completed");
}
</xpp> Any feedback welcome.
Regards, harald (--Hghrp 03:00, 20 March 2006 (PST))
Additional links[edit]
Used this article to create a more user friendly approach to this through a wizard and fixing some additional problems.
