During the data migration from Ax 2012 to D365, the table might be renamed and there might be record relations by RecId to another table. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)
In this scenario, it is necessary to develop a script in order to move Ax 2012 data as it is (including the "RecId" numbers) in order to save the data consistency. Otherwise, the migrated data can be inconsistent since record id references can point to not existing records. The script should be executed on the “PreSync” stage when both tables (“AATable” and “BBTable”) exist in the database.
When the data upgrade process is completed and you create a new record in the "BBTable" you can see the error: "Cannot create a record in "BBTable." The record already exists." But you see that there are no violations from a primary key perspective. Most probably there is a RecId sequence issue.
In order to prevent this error, it is needed to align record id numbers (RecId) after executing the data upgrade script. I would recommend performing this operation on the "PostSync" stage or via job when data migration is finished.
First of all, it is good to know the “RecId” number sequence name. In D365 Microsoft has switched from using a dedicated database table to using the standard SQL sequences functionality. So, we need to identify the SQL “RecId” sequence for the table. The following method can be used:
///
<summary>
///
Defines the SQL sequence name of the given table.
///
</summary>
///
<param name = "_tableName">
///
The name of the table.
///
</param>
///
<returns>
///
Returns the SQL sequence name of the given table.
///
</returns>
public str getSequenceProcedureName(TableName
_tableName)
{
str
result;
str ret;
TableId tableId;
if (this.isTablePresent(_tableName))
{
str sqlQuery = strFmt(@"
SELECT ID from TABLEIDTABLE
JOIN sys.sequences
ON
sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)
WHERE TABLEIDTABLE.NAME =
'%1'",
_tableName);
result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
if (result)
{
tableId = str2Int(result);
if (tableId)
{
ret = strFmt('SEQ_%1',tableId);
}
}
}
return ret;
}
///
<summary>
///
Defines, whether the table exists in the database.
///
</summary>
///
<param name = "_tableName">
///
The name of the table.
///
</param>
///
<returns>
///
True if the table exists, otherwise false.
///
</returns>
public boolean isTablePresent(TableName _tableName)
{
str sqlQuery = strFmt(@"
IF OBJECT_ID ('[dbo].[%1]', 'U') IS
NULL
SELECT 0
ELSE
SELECT 1",
_tableName);
str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
return str2Int(result);
}When we know the sequence name, we can align the RecId number sequence with the method:
///
<summary>
///
Updates the appropriate RecId number sequence for the given table.
///
</summary>
///
<param name = "_tableName">
///
The name of the table.
///
</param>
///
<returns>
/// True if the sequence updated, otherwise false.
/// </returns>
public boolean alignNumberSequence(TableName _tableName)
{
boolean ret;
str triggerName;
RecId curMaxRecId;
RecId newMaxRecId;
if (_tableName)
{
triggerName
= this.getSequenceProcedureName(_tableName);
curMaxRecId = this.getMaxRecId(_tableName);
if (triggerName && curMaxRecId)
{
newMaxRecId = curMaxRecId + 1;
str sqlQuery = strFmt(@"ALTER SEQUENCE
[dbo].[%1]
MINVALUE %2 RESTART WITH %2",
triggerName,
newMaxRecId);
ReleaseUpdateDB::statementExeUpdate(sqlQuery);
sqlQuery = strFmt(@"SELECT minimum_value
from sys.sequences
WHERE
sys.sequences.name = '%1'",
triggerName);
str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
if (result)
{
curMaxRecId =
str2Int64(result);
if (curMaxRecId == newMaxRecId)
{
ret = true;
}
}
}
}
return ret;
}
I’ll be glad if you find this helpful.
No comments:
Post a Comment