I have a table that I created awhile ago that all of a sudden we cannot insert records into in our test environment. When I try to insert a new record into this table I get the error message 'Cannot create a record in {Table Name}. The record already exists.
I used SQL Profiler to trace the SQL statement being sent to the database and found out that it is trying to insert a record into the table with the same RecId as an existing record in the table. I figured that this must be an issue in the SYSTEMSEQUENCES table so I went into the AOT to get the Id for the table. I confirmed that I had the correct table Id by running this SQL statement:
SELECT *
FROM SQLDICTIONARY
WHERE NAME = '{Table Name}'
AND FIELDID = 0
I then ran this SQL statement to see what the SYSTEMSEQUENCES table thinks should be the next RECID:
SELECT *
FROM SYSTEMSEQUENCES
WHERE TABID = {Table Id}
The weird thing is that the value that the SYSTEMSEQUENCES table says should be the next RECID is correct, however that is not the value that Dynamics AX is using when it tries to create a new record, it is using a value that has already been used.
Could somebody give me some suggestions on what might be wrong and how to fix it? I did try restarting the server and deleting all of the AUC files on my local computer.
Thank You
*This post is locked for comments