Check and Fix the Next AX Record ID for All Tables

** This was tested on AX 4.0 SP2

The following script will check and update the next record ID for every table in AX.  This is useful if you copied data from one environment to another.  After running the script you must restart the AOS.  At the end you’ll see a log of what it changed, for example:

ADDRESS did not need to be updated.
APPACTION did not need to be updated.
….

The SQL Statement:

SETNOCOUNT ON
DECLARE @COMPANY VARCHAR(3)
SET @COMPANY = ‘510’

IF (NOT EXISTS ( SELECT *
FROM DATAAREA
WHERE DATAAREA.ID = @COMPANY ))
BEGIN
PRINT ‘ERROR: Company doesn”t exist’
RETURN
END

PRINT ‘You must restart the AOS after running this script’

DECLARE @NUMROWS INT
DECLARE @TABLE_NAME SYSNAME
DECLARE @SQL VARCHAR(MAX)



DECLARE table_name_cursor CURSOR
— Determine which tables have a RecID column

FOR

SELECT tables.name
FROM sys.tables WITH (NOLOCK)
WHERE EXISTS ( SELECT *
FROM sys.columns WITH (NOLOCK)
WHERE columns.OBJECT_ID = tables.OBJECT_ID
AND columns.NAME = ‘DATAAREAID’ )
AND EXISTS ( SELECT *
FROM sys.columns WITH (NOLOCK)
WHERE columns.OBJECT_ID = tables.OBJECT_ID
AND columns.NAME = ‘RecID’ )
AND EXISTS ( SELECT *
FROM SQLDICTIONARY WITH (NOLOCK)
WHERE SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = tables.name )
AND tables.NAME NOT LIKE ‘AIF%’
AND tables.NAME NOT LIKE ‘DEL_%’
AND tables.NAME <> ‘SYSDATABASELOG’
AND tables.NAME <> ‘SYSTEMSEQUENCES’
ORDER BY tables.NAME

OPEN table_name_cursor

FETCH NEXT FROM table_name_cursor
INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT

IF ((

SELECT COUNT(*)
FROM [‘ + @TABLE_NAME + ‘] WITH (NOLOCK)
WHERE [‘ + @TABLE_NAME + ‘].DATAAREAID = ”’ + @COMPANY + ”’
) > 0

AND (
SELECT COUNT(*)
FROM SYSTEMSEQUENCES WITH (NOLOCK)
INNER JOIN SQLDICTIONARY WITH (NOLOCK)
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ”’ + @TABLE_NAME + ”’
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
) = 1)

BEGIN

SELECT @MaxRecID = MAX(RECID)
FROM [‘ + @TABLE_NAME + ‘] WITH (NOLOCK)
WHERE [‘ + @TABLE_NAME + ‘].DATAAREAID = ”’ + @COMPANY + ”’

SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES WITH (NOLOCK)
INNER JOIN SQLDICTIONARY WITH (NOLOCK)
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ”’ + @TABLE_NAME + ”’
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

IF (@NextVal > @MaxRecID)

BEGIN

PRINT ”’ + @TABLE_NAME + ‘ did not need to be updated.”
END

ELSE

BEGIN

PRINT ”Updated ‘ + @TABLE_NAME + ‘ from ” + CONVERT(VARCHAR(MAX), @NextVal) + ” to ”
+ CONVERT(VARCHAR(MAX), @MaxRecID + 1)

UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ”’ + @TABLE_NAME + ”’
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

END

END’

–PRINT @SQL
EXEC (@SQL)

FETCH NEXT FROM table_name_cursor
INTO @TABLE_NAME
END

CLOSE table_name_cursor;
DEALLOCATE table_name_cursor;