Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.
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.
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
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
fetch next from dict into @t, @taot, @tsql
while @@FETCH_STATUS = 0
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
print N'tableid: ' + convert(nvarchar(28),@t) + N' ' + @tsql + N' recid: ' + convert(nvarchar(28),coalesce(@maxrecid,0)) + N' nextvalue: ' + convert(nvarchar(28),@nextval)
fetch next from dict into @t, @taot, @tsql
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.
Business Applications communities