Analytical Accounting Primary Key Problems

Personally I used to have allot of primary key issues when working with analytical accounting due to errors in the Analytical Accounting Index Master table in Dynamics database, and used to open this table manually and fill the correct indexes there.

Today @ Interesting Findings & Knowledge Sharing blog, “veeyeskay” posted an SQL script that updates this table Dynamically! Find the script below or visit the post for more information here.

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(aaTrxDimCodeBoolID),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

Regards,
--
Mohammad R. Daoud
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
CTO
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com