Hi,
We are upgrading data from AX 2012 R3 CU13 to Dynamics 365 Finance and Operations. The data upgrade package fails at step no# 9 (postsync for data upgrade).
We found that this step is running a power shell script called (AutoDataUpgradePostSync.ps1), after failure we checked ReleasUpdateScriptErrorLog table and found the following error:
CLASSNAME: ReleaseUpdateDB72_Project
METHODNAME: UpdateProjCostTransWorkerToResource
Cannot select a record in Expense (ProjCostTrans). Project date: . The SQL database has issued an error. Object Server DynamicsAXBatchManagement: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'FROM'. SELECT FROM PROJCOSTTRANS T1 WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND 1=?) GROUP BY T1.DATAAREAID ORDER BY T1.DATAAREAID session 12 (Admin) Batch task failed: Cannot select a record in Expense (ProjCostTrans). Project date: . The SQL database has issued an error.
We checked this method as shown below and it seems to be OK, We don't know how is the syntax incorrect !! How to fix!! Is it a bug from Microsoft side!!
Please advise and thanks
/// <summary>
/// Convert the worker field to resource for <c>projCostTrans</c> table.
/// </summary>
[UpgradeScriptDescriptionAttribute("@ProjDataUpgrade72:UpgradeWorkerToResourceForProjCostTrans"),
UpgradeScriptConfigKeyAttribute(configurationKeyStr(Project)),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(ProjCostTrans), false, true, true, false),
UpgradeScriptTableAttribute(tableStr(ResResourceIdentifier), true, true, false, false),
UpgradeScriptTableAttribute(tableStr(WrkCtrTable), true, true, false, false),
UpgradeScriptTableAttribute(tableStr(HcmWorker), false, true, false, false),
UpgradeScriptTableAttribute(tableStr(CompanyInfo), false, true, false, false),
UpgradeDependsOnTaskAttribute(methodStr(ReleaseUpdateDB72_Project, updateProjCostSalesPriceWorkerToResource))]
public void updateProjCostTransWorkerToResource()
{
ProjCostTrans projCostTrans;
ProjCostTrans updateTable;
this.callInsertUpdateRelatedSkipMethods(updateTable);
// Update for the case of fields Worker and WorkerLegalEntity fields are not 0.
while select Worker, WorkerLegalEntity from projCostTrans
group by Worker, WorkerLegalEntity
where projCostTrans.Worker != 0
&& projCostTrans.WorkerLegalEntity != 0
{
RefRecId resoruceLegalEntity = projCostTrans.WorkerLegalEntity;
RefRecId resource = ReleaseUpdateDB72_Project::findOrCreateByWorker(projCostTrans.Worker, resoruceLegalEntity);
update_recordset updateTable
setting Resource = resource
where updateTable.Worker == projCostTrans.Worker
&& updateTable.WorkerLegalEntity == resoruceLegalEntity;
}
// Update for the case of fields Worker is not 0 and WorkerLegalEntity is 0.
while select Worker, DataAreaId from projCostTrans
group by Worker, DataAreaId
where projCostTrans.Worker != 0
&& projCostTrans.WorkerLegalEntity == 0
{
RefRecId resoruceLegalEntity = CompanyInfo::findDataArea(projCostTrans.DataAreaId).RecId;
RefRecId resource = ReleaseUpdateDB72_Project::findOrCreateByWorker(projCostTrans.Worker, resoruceLegalEntity);
update_recordset updateTable
setting Resource = resource
where updateTable.Worker == projCostTrans.Worker
&& updateTable.WorkerLegalEntity == 0
&& updateTable.DataAreaId == projCostTrans.DataAreaId;
}
}
