The function I mentioned crashes our upgrades to BC19 on a regular basis. - It lets the SQL Server run out of Memory (16GB) after about 12 Hours. on a single company with just 22k Items. The code in this upgrade codeunit doesn't look too bad, but is very inefficient as it loops every Items and reads the groups whenever an item has a Prod Group set instead of doing it the other way around
so my suggestion is to replace your current function with this:
procedure UpgradeItemPostingGroups()
var
Item: Record "Item";
GenProdPostingGroup: Record "Gen. Product Posting Group";
InventoryPostingGroup: Record "Inventory Posting Group";
UpgradeTagDefinitions: Codeunit "Upgrade Tag Definitions";
UpgradeTag: Codeunit "Upgrade Tag";
begin
if UpgradeTag.HasUpgradeTag(UpgradeTagDefinitions.GetItemPostingGroupsUpgradeTag()) then
exit;
GenProdPostingGroup.SetLoadFields(Code);
if GenProdPostingGroup.FindSet() then
repeat
item.SetRange("Gen. Prod. Posting Group", GenProdPostingGroup.Code);
Item.ModifyAll("Gen. Prod. Posting Group Id", GenProdPostingGroup.SystemId);
until GenProdPostingGroup.Next() = 0;
InventoryPostingGroup.SetLoadFields(Code);
if InventoryPostingGroup.FindSet() then
repeat
item.SetRange("Inventory Posting Group", InventoryPostingGroup.Code);
Item.ModifyAll("Inventory Posting Group Id", InventoryPostingGroup.SystemId);
until InventoryPostingGroup.Next() = 0;
UpgradeTag.SetUpgradeTag(UpgradeTagDefinitions.GetItemPostingGroupsUpgradeTag());
end;
p.s. the file you need to edit is found in \Upgrade\UpgradeBaseApp.Codeunit.al
Comments
To circumvent the script for my current update, i wrote some powershell/SQL code to to the same data alterations as the function mentioned above (sorry about the typo)
In case someone else needs it: you have to execute this AFTER the App synchronization and BEFORE Start-NAVDataUpgrade
Execution time is below 1 Second per Company. - Use at your own risk & don't execute any code you don't understand
$UpgTag = "MS-405484-GenItemPostingGroups-20210719"
$CompanyList = (Invoke-Sqlcmd -ServerInstance $SqlServerInstance -Database $DatabaseName -Credential $DatabaseCredential -Query "SELECT Name FROM [Company]").Name
foreach ($Company in $CompanyList) {
# set tag,
# set SystemId of Inventory Posting Group to new
$Query = "
insert into [Upgrade Tags`$63ca2fa4-4f03-4f2b-a480-172fef340d3f] (Tag, Company, [Tag Timestamp], [Skipped Upgrade])
VALUES ('$UpgTag', '$($Company.ToUpper())', CURRENT_TIMESTAMP, 0)
update item
set item.[Inventory Posting Group Id] = ipg.[`$systemId]
from [$Company`$Item`$437dbf0e-84ff-417a-965d-ed2bb9650972] item
inner join [$Company`$Inventory Posting Group`$437dbf0e-84ff-417a-965d-ed2bb9650972] ipg
on ipg.Code = item.[Inventory Posting Group]
update item
set item.[Gen_ Prod_ Posting Group Id] = gppg.[`$systemId]
from [$Company`$Item`$437dbf0e-84ff-417a-965d-ed2bb9650972] item
inner join [$Company`$Gen_ Product Posting Group`$437dbf0e-84ff-417a-965d-ed2bb9650972] gppg
on gppg.Code = item.[Gen_ Prod_ Posting Group]"
Invoke-Sqlcmd -ServerInstance $SqlServerInstance -Database $DatabaseName -Credential $DatabaseCredential -Query $Query
}
Category: Data Migrations
Business Central Team (administrator)
Thank you for this suggestion! Currently this is not on our roadmap. We are tracking this idea and if it gathers more votes and comments we will consider it in the future. Best regards, Business Central Team