Handling Primary Key Errors in Analytical Accounting

Hi all…

It has been some time, since I have posted something here… Was busy at many other assignments. However, here comes an article which will be of interest and use to many, especially those who are using Analytical Accounting module of Microsoft Dynamics GP.

‘How many times have we received the errors of primary key violations in AAG10000, AAG20000, AAG30000 etc, when creating and posting transactions in Analytical Accounting? Usually,  these kind of errors come when we have restored a production database into a test database for the purpose of some testing.

These errors occur because of the fact that the AA Index Master table which is present in the DYNAMICS database is out of sync with the actual database indexes. Let me explain a little more on this.

All records in AA tables are indexed with an integer value as the key field. This key value is an incrementing integer starting from 1. The last used key value for every master and transaction table is stored in the DYNAMICs database in the table AAG00102.

This table contains the Company ID, the Table Series Value and the last used index value. Every time a record is added into0 the AA table, this master table is referred for the last used index value for that specific table series and generates the next value for saving the record. Since this table is maintained in the DYNAMICS database, every time a production company is restored in the test company, this table values goes out of sync for the test company.

I have attached a procedure below, which does update the AAG00102 table with the proper index values for all companies that are installed in GP. This procedure syncs up the table so that the primary key errors are eliminated. Whenever there is a restore of a production database into the test database, this script needs to be executed. It can be modified to execute it for a specific company values as well.

NOTE: All users must be out of Dynamics GP before this script is executed. Also take a backup of the DYNAMICS database before this script is being executed.

USE [DYNAMICS]
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[sp_FixAARecordCount]')
                    AND type IN ( N'P', N'PC' ) )
    DROP PROCEDURE [dbo].[sp_FixAARecordCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_FixAARecordCount]
AS
    DECLARE @INTERID CHAR(5)
    DECLARE @CMPANYID INT
    DECLARE @SQL CHAR(8000)

    DECLARE cr_Companies CURSOR
        FOR SELECT  INTERID,
                    CMPANYID
            FROM    DYNAMICS.dbo.SY01500
            ORDER BY CMPANYID
    OPEN cr_Companies
    FETCH NEXT FROM cr_Companies INTO @INTERID, @CMPANYID
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAcctClassID),0) FROM '
                  + @INTERID
                  + '..AAG00201) WHERE  aaTableID = 201 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDistrQueryID),0) FROM '
                  + @INTERID
                  + '..AAG00301) WHERE  aaTableID = 301 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaMLQueryID),0) FROM '
                  + @INTERID
                  + '..AAG00310) WHERE  aaTableID = 310 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimID),0) FROM '
                  + @INTERID
                  + '..AAG00400) WHERE  aaTableID = 400 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeID),0) FROM '
                  + @INTERID
                  + '..AAG00401) WHERE  aaTableID = 401 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeNumID),0) FROM '
                  + @INTERID
                  + '..AAG00402) WHERE  aaTableID = 402 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM  '
                  + @INTERID
                  + '..AAG00403) WHERE  aaTableID = 403 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeDateID),0) FROM  '
                  + @INTERID
                  + '..AAG00404) WHERE  aaTableID = 404 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDateID),0) FROM '
                  + @INTERID
                  + '..AAG00500) WHERE  aaTableID = 500 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTreeID),0) FROM '
                  + @INTERID
                  + '..AAG00600) WHERE  aaTableID = 600 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaNodeID),0) FROM '
                  + @INTERID
                  + '..AAG00601) WHERE  aaTableID = 601 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaOption),0) FROM '
                  + @INTERID
                  + '..AAG00700) WHERE  aaTableID = 700 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAliasID),0) FROM '
                  + @INTERID
                  + '..AAG00800) WHERE  aaTableID = 800 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaBudgetTreeID),0) FROM '
                  + @INTERID
                  + '..AAG00900) WHERE  aaTableID = 900 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaUDFID),0) FROM '
                  + @INTERID
                  + '..AAG01000) WHERE  aaTableID = 1000 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLWorkHdrID),0) FROM '
                  + @INTERID
                  + '..AAG10000) WHERE  aaTableID = 10000 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaSubLedgerHdrID),0) FROM '
                  + @INTERID
                  + '..AAG20000) WHERE  aaTableID = 20000 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            EXEC
                ( 'UPDATE  DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLHdrID),0) FROM '
                  + @INTERID
                  + '..AAG30000) WHERE  aaTableID = 30000 AND CMPANYID = '
                  + @CMPANYID + ''
                )
            FETCH NEXT FROM cr_Companies INTO @INTERID, @CMPANYID
        END
    CLOSE cr_Companies
    DEALLOCATE cr_Companies
GO
GRANT EXECUTE ON sp_FixAARecordCount TO  DYNGRP

Hope you guys find this article useful.

Until next post…