End of mainstream support for Microsoft Dynamics AX 2009, 2012, and 2012Mainstream support for Dynamics AX 2009 Service Pack 1 (SP1), Dynamics AX 2012, and Dynamics AX 2012 R2 ended Oct. 9, 2018. After that date, only security hotfixes will be provided for these three versions through the extended support period that until Oct. 12, 2021. Read more
Dynamics 365 2019 release wave 2 plan Discover the latest updates to Dynamics 365.Release Plan | Weekly Deployment Notes
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants.Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements.
ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent 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