4

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

STATUS DETAILS
Needs Votes
Ideas 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

Comments

S

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-NAVDataUpgradeExecution 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