SBX - Search With Button

SBX - Forum Post Title

Updating SystemSequences table?

Microsoft Dynamics AX Forum

Khan asked a question on 17 Jul 2013 3:44 AM
My Badges

Question Status

Verified

Hi,

I was not able to create new records in certain tables and each time got a duplicate record error.

On investigation, I found out that the SystemSequence table is showing incorrect value of the next recId.

The value its showing in the "next" column already exists in my table, which is why I am getting this error.

I need to know, is there a way I could update the SystemSequence table for all the tables in AX?

Cause I don't know where else we could get this behavior.

Thanks.

Reply
Brandon Wiese responded on 17 Jul 2013 4:40 AM
My Badges
Suggested Answer

I have seen this before, and in most cases fixing it did indeed resolve the issue permanently though the root cause was left something of a mystery.  This assumes, of course, that you're not inserting records directly outside of AX.

One scenario that does come to mind for WHY this happens centered around the upgrade from earlier versions of AX.  During the upgrade from AX 3.0 to AX 4.0, RecId's went from being company specific within each table (where the same RecId could exist within each company in the same table), to being just table based (RecId unique within each table).  The 3.0 to 4.0 upgrade process actually renumbers all of your RecId's in all tables, fixing references to them in other tables, a complex process.

I have seen both the renumbering process fail, as well as the SystemSequences clean-up process fail.  The clean-up of SystemSequences involves removing all of the table-specific RecId records in non-DAT companies, leaving only DAT company records.  If you find non-DAT records in your SystemSequences tables on or after 4.0, they do not belong and are a source of serious problems, including what you are describing.  Work with your partner and Microsoft to resolve this situation if you have it.

I have a simple SQL script that I use to detect this and I will post it below.  The fix is as simple as shutting down the AOS, fixing the SystemSequences records to start beyond the last used RecId for each table, and restarting the AOS.  Note that the AOS actually consumes blocks of RecId's, usually 256 at a time, and so shutting it down during this maintenance is absolutely critical to success.

Reply
Brandon Wiese responded on 17 Jul 2013 4:44 AM
My Badges
Verified Answer

This SQL loops through all tables in an AX business database (should work for versions 4.0, 2009, and 2012 with little or no modification), using the SQLDICTIONARY table as a mean of translating tableId's found in SYSTEMSEQUENCES into table names (in AX 2012 and beyond this can also be done from the ModelElements table in the model store database).  There are tables in AX not found in either SQLDICTIONARY or ModelElements which may need to be considered through other means.

Use this SQL at your own risk.  It is provided for academic purposes only.

declare @t INT  -- TableId

declare @taot NVARCHAR(40) -- TableName (AOT)

declare @tsql NVARCHAR(40) -- identifierstr (SQL)

declare @cmd NVARCHAR(4000)

declare @maxrecid  bigint

declare @nextval bigint

-- loop through all tables references in SQLDICTIONARY with a DATAAREAID field

declare dict insensitive cursor for

 select TABLEID, NAME, SQLNAME

   from SQLDICTIONARY

   where FIELDID = 0

     and SQLNAME in (select t.name

                       from sys.tables t

                       join sys.syscolumns c on c.id = t.object_id

                       where c.name = N'RECID')

     and SQLNAME not in (N'SYSTEMSEQUENCES',N'SQLSYNCINFO')  -- special table

   order by TABLEID

open dict

fetch next from dict into @t, @taot, @tsql

while @@FETCH_STATUS = 0

begin

 set @cmd = N'select @ret = MAX(RECID) from ' + @tsql

 exec sp_executesql @cmd, N'@ret bigint output', @maxrecid output

 set @cmd = N'select @ret = NEXTVAL from SYSTEMSEQUENCES where ID=-1 and TABID=' +convert(nvarchar(28), @t)

 exec sp_executesql @cmd, N'@ret bigint output', @nextval output

 if coalesce(@maxrecid,0) >= @nextval

 begin

   print N'tableid: ' + convert(nvarchar(28),@t) + N' ' + @tsql + N' recid: ' + convert(nvarchar(28),coalesce(@maxrecid,0)) + N' nextvalue: ' + convert(nvarchar(28),@nextval)

 end

 fetch next from dict into @t, @taot, @tsql

end

close dict

deallocate dict

Reply
Khan responded on 17 Jul 2013 10:40 PM
My Badges

Thanks a lot Brandon. I learnt a lot from your post.

However, in my case the client inserted data directly into Sql server.

To resolve this, I simply had to grab the max recid of that table, then update the next recid in the SystemSequence table.

Reply
Brandon Wiese responded on 17 Jul 2013 4:44 AM
My Badges
Verified Answer

This SQL loops through all tables in an AX business database (should work for versions 4.0, 2009, and 2012 with little or no modification), using the SQLDICTIONARY table as a mean of translating tableId's found in SYSTEMSEQUENCES into table names (in AX 2012 and beyond this can also be done from the ModelElements table in the model store database).  There are tables in AX not found in either SQLDICTIONARY or ModelElements which may need to be considered through other means.

Use this SQL at your own risk.  It is provided for academic purposes only.

declare @t INT  -- TableId

declare @taot NVARCHAR(40) -- TableName (AOT)

declare @tsql NVARCHAR(40) -- identifierstr (SQL)

declare @cmd NVARCHAR(4000)

declare @maxrecid  bigint

declare @nextval bigint

-- loop through all tables references in SQLDICTIONARY with a DATAAREAID field

declare dict insensitive cursor for

 select TABLEID, NAME, SQLNAME

   from SQLDICTIONARY

   where FIELDID = 0

     and SQLNAME in (select t.name

                       from sys.tables t

                       join sys.syscolumns c on c.id = t.object_id

                       where c.name = N'RECID')

     and SQLNAME not in (N'SYSTEMSEQUENCES',N'SQLSYNCINFO')  -- special table

   order by TABLEID

open dict

fetch next from dict into @t, @taot, @tsql

while @@FETCH_STATUS = 0

begin

 set @cmd = N'select @ret = MAX(RECID) from ' + @tsql

 exec sp_executesql @cmd, N'@ret bigint output', @maxrecid output

 set @cmd = N'select @ret = NEXTVAL from SYSTEMSEQUENCES where ID=-1 and TABID=' +convert(nvarchar(28), @t)

 exec sp_executesql @cmd, N'@ret bigint output', @nextval output

 if coalesce(@maxrecid,0) >= @nextval

 begin

   print N'tableid: ' + convert(nvarchar(28),@t) + N' ' + @tsql + N' recid: ' + convert(nvarchar(28),coalesce(@maxrecid,0)) + N' nextvalue: ' + convert(nvarchar(28),@nextval)

 end

 fetch next from dict into @t, @taot, @tsql

end

close dict

deallocate dict

Reply
Brandon Wiese responded on 17 Jul 2013 4:40 AM
My Badges
Suggested Answer

I have seen this before, and in most cases fixing it did indeed resolve the issue permanently though the root cause was left something of a mystery.  This assumes, of course, that you're not inserting records directly outside of AX.

One scenario that does come to mind for WHY this happens centered around the upgrade from earlier versions of AX.  During the upgrade from AX 3.0 to AX 4.0, RecId's went from being company specific within each table (where the same RecId could exist within each company in the same table), to being just table based (RecId unique within each table).  The 3.0 to 4.0 upgrade process actually renumbers all of your RecId's in all tables, fixing references to them in other tables, a complex process.

I have seen both the renumbering process fail, as well as the SystemSequences clean-up process fail.  The clean-up of SystemSequences involves removing all of the table-specific RecId records in non-DAT companies, leaving only DAT company records.  If you find non-DAT records in your SystemSequences tables on or after 4.0, they do not belong and are a source of serious problems, including what you are describing.  Work with your partner and Microsoft to resolve this situation if you have it.

I have a simple SQL script that I use to detect this and I will post it below.  The fix is as simple as shutting down the AOS, fixing the SystemSequences records to start beyond the last used RecId for each table, and restarting the AOS.  Note that the AOS actually consumes blocks of RecId's, usually 256 at a time, and so shutting it down during this maintenance is absolutely critical to success.

Reply

SBX - Two Col Forum

SBX - Migrated JS