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:
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;
