Getting "GetAccountsFull" error when accessing YO account groups due to duplicate CommentID's and AddressID's

Summary

This error comes up when viewing or clicking one of the account groups from the account list.

This is due to accounts sharing the same AddressID and CommentID which are supposed to be unique.

Fix

There are two ways to address the issue:

Edit the GetAccountsFull stored procedure
Update the affected accounts via account export and import.

Editing the GetAccountsFull procedure

Open up SSMS
Run the following script:

ALTER PROCEDURE [dbo].[GetAccountsFull]
 
    @ParentId INTEGER,
    @IsParentTypes INTEGER,
    @IncludeSubItems BIT,
    @IncludeInactive BIT,
    @Sorted BIT,
    @IncludeImageData BIT
 
AS
BEGIN
 
--   Return ResultSets
--   oAccount = 0
--   oParent = 1
--   oAddress = 2
--   oComment = 3
--   oMembership = 4
--   oRenewal = 5
--   oImageData = 6
--   oEarnProfile = 7
 
    DECLARE @ValidIsParentTypes TABLE(IsParent INTEGER)
 
    -- eINCLUDEPARENTS.ItemsOnly
    IF @IsParentTypes = 0
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (0)
    END
 
    -- eINCLUDEPARENTS.AllItemsAndAllParents
    IF @IsParentTypes = 1
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (0), (1), (2), (3)
    END
 
    -- eINCLUDEPARENTS.AllParentsOnly
    IF @IsParentTypes = 2
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (1), (2), (3)
    END
 
    -- eINCLUDEPARENTS.ParentsOfItemsOnly
    IF @IsParentTypes = 3
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (1), (3)
    END
 
    -- eINCLUDEPARENTS.ParentsOfParentsOnly
    IF @IsParentTypes = 4
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (2), (3)
    END
 
    
    -- N.B. Can't use SELECT INTO with the same temp table from within separate if clauses so use defined table variable as intermediate
    DECLARE @Accounts TABLE(AccountID INTEGER)
 
 
    -- Create base account data
    IF @IncludeSubItems = 0
    BEGIN
 
        INSERT INTO @Accounts
        SELECT a.AccountID
        FROM Account AS a
            JOIN @ValidIsParentTypes AS v ON (v.IsParent = a.IsParent)
        WHERE (a.ParentID = @ParentId OR @ParentId = 0)
 
    END
    ELSE
    BEGIN
 
        WITH AccountChain(ParentID, AccountID, IsParent) AS ( 
            SELECT a1.ParentID, a1.AccountID, a1.IsParent 
            FROM Account AS a1
            WHERE a1.ParentID = @ParentId
            UNION ALL 
            SELECT a2.ParentID, a2.AccountID, a2.IsParent 
            FROM Account AS a2 INNER JOIN AccountChain c ON a2.ParentID = c.AccountID
            )
        INSERT INTO @Accounts
        SELECT a.AccountID
        FROM Account AS a
            JOIN AccountChain AS ac ON (ac.AccountID = a.AccountID)
            JOIN @ValidIsParentTypes AS v ON (v.IsParent = ac.IsParent)
 
    END
 
 
    SELECT a.*
    INTO #Accounts
    FROM Account AS a
        JOIN @Accounts AS t ON(t.AccountID = a.AccountID)
    WHERE (a.Status < 4 OR @IncludeInactive = 1)            -- eAccountStatus.Inactive
 
    -- Select Account ordered if required
    IF @Sorted = 0
    BEGIN
        SELECT * FROM #Accounts
    END
    ELSE
    BEGIN
        SELECT * FROM #Accounts
        ORDER BY LastName ASC
    END
 
    -- Get Account parent records
    SELECT DISTINCT p.* FROM Account AS p
        JOIN #Accounts AS a ON (a.ParentID = p.AccountId)
 
    -- Get Account Address
    SELECT DISTINCT ad.* 
    FROM Address AS ad
        INNER JOIN #Accounts AS a ON (ad.AddressID = a.AddressID)
 
    -- Get Account Comment
    SELECT DISTINCT c.* 
    FROM Comment AS c
        INNER JOIN #Accounts AS a ON (a.CommentID = c.CommentID)
 
    -- Get Account Membership
    SELECT DISTINCT m.* 
    FROM Membership AS m
        INNER JOIN #Accounts AS a ON (a.MembershipID = m.MembershipID)
 
    -- Get Account Renewal
    SELECT DISTINCT r.* 
    FROM Renewal AS r
        INNER JOIN #Accounts AS a ON (a.RenewalID = r.RenewalID)
 
    -- Get Account ImageData
    SELECT DISTINCT i.* 
    FROM  ImageData AS i
        INNER JOIN #Accounts AS a ON (a.ImageID = i.ImageDataID)
    WHERE @IncludeImageData = 1
 
    -- Get Account PointsProfile
    SELECT DISTINCT p.* 
    FROM PointsProfile AS p
        INNER JOIN #Accounts AS a ON (a.PointsProfile = p.PointsProfileID)
 
    
    -- Cleanup
    DROP TABLE #Accounts
END

Update the affected accounts via account export and import.

Identify which accounts or account groups are affected and export the account details in a csv file.

Prior to making any changes, a backup of the database must be saved on a different folder/location than of the default backup location.

Export the account details via the Export Accounts job.
Import the saved report Account Export Full:
Open the report and save the report.
Edit the view and note the name of the view. Make sure there are no pinned columns
Note down the name of the view as it will be used in the export account job.
Create an export file by account group if multiple groups are affected. Name each file with the account group name for easy reference.
Make sure to set the filename and location. Also make sure to set the correct account group to export.
Set Delimiter as Comma
Review the exported files and make sure the information is in the correct fields. Refer to Account / Supplier Import Specification in MyBepoz and make sure to follow the Extended Full Account Import File Layout.
Once the files are exported and reviewed, go to SSMS and update each of the groups to have a CommentID and AddressID of 0
- Update Account
  Set AddressID = 0, CommentID = 0
  Where parentid = **the parentID throwing the error
Add an Import Account job. Import the file generated from the export previously and import by account group. This will create new AddressIDs for the accounts with the address/contact information of the old AddressIDs
Make sure to select Extended for the Import Format
Run the import job for each of the account group affected. There should be a different file for each of the group to update.
Review the account list and make sure the error is gone and the address details are imported back.

Getting "GetAccountsFull" error when accessing YO account groups due to duplicate CommentID's and AddressID's

Attachments: