I've seen this error a couple times within the past week after we migrated our data from PROD into DEV
"Cannot create a record in <table description> (<table name>). The record already exists."
What I was finding is the index was ok and everything was unique but for some odd reason we kept getting this error, even though I did a search on the data that was being inserted and it did not exist like the system was telling us.
What I figured out is sometimes the recid count gets messed up with transferring data and you need to reset it to a value greater than the max rec id of the current table. Here are the steps to fix it.
1. Create a Job to get the table id of the table having problems inserting data
static void GetTableIdFromNameJob(Args _args)
{
info(strFmt("%1", tableName2id("<table name>")));
}
2. In sql mgt studio
select max(recid) from <table name from step 1>
3. In sql mgt studio
Edit table SYSTEMSEQUENCES
Select the record where tabid = '<table id from info box in step 1>
Take the max id from step 2 and add 100 to it to be safe and update the field 'nextval' from systemsequences on the tableid you have selected.
4. Restart AOS (the changes will not take affect until you restart the AOS)
Or you can just go into SQL management studio and use this code:
ReplyDeleteupdate SYSTEMSEQUENCES
set NEXTVAL = (select MAX(RecId) from INVENTTABLE) + 1
where TABID = 175
You can find the TABID (Table id) using SQL dictionary (make sure the field Id is 0)
e.g.: select * from SQLDICTIONARY where FIELDID = 0 and NAME = 'InventTable'
Note: AOS should be turned off when making changes to the SYSTEMSEQUENCES table
I never knew that you could pull the name from a flat table, thanks. I take it this is the table that the function tablename2id() relies on? Also do you know what causes this to occur? Its the first time I've ever ran across it but we've migrated data to the environment multiple times so I'm not too sure what caused it.
ReplyDeleteThanks for your posting, just create some SQL statements below to check and fix this issue.
ReplyDelete--To check if there is issue in table SystemSequences, the max recid in table is equal or great to the nextVal in table SystemSequences
use TST --Database name
declare @showIssueOnly int;
declare @doUpdate int;
declare @tableID int;
declare @tableName nvarchar(80);
declare @nextVal BigInt;
declare @maxRecId BigInt;
declare @sqlStatement as nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
declare @db_cursor as cursor;
set @showIssueOnly = 1; --Switch to show all or issue only, 0 - show all, 1 - show issue only;
set @doUpdate = 0; --Switch to apply update method, 0 - no update, 1 - update;
SET @ParmDefinition = N'@maxRecIdOut BigInt OUTPUT'
print 'Table name, Table Id, Max Rec Id, Next Value, Next - Max Rec';
set @db_cursor = cursor for
select A.tableid, a.NAME, B.NEXTVAL
from SQLDICTIONARY A, SystemSequences B
where A.FIELDID = 0
and A.TABLEID = B.TABID
open @db_cursor;
fetch next from @db_cursor into @tableID, @tableName, @nextVal;
while @@FETCH_STATUS = 0
begin
select @sqlStatement = 'select @maxRecIdOut = max(recid) from ' + @tableName;
EXECUTE sp_executesql @sqlStatement, @ParmDefinition, @maxRecIdOut=@maxRecId OUTPUT
select @maxRecId = isnull(@maxRecId, 0);
if (((@showIssueOnly = 1) and ((@nextVal - @maxRecId) <= 0))
or (@showIssueOnly != 1))
begin
print @tableName + ', ' + cast(@tableId as varchar(10)) +', ' + cast(@maxRecId as varchar(50)) + ', ' + cast(@nextVal as varchar(50)) + ', ' + cast((@nextVal - @maxRecId) as varchar(50));
end
if ((@doUpdate = 1) and ((@nextVal - @maxRecId) <= 0))
begin
update SystemSequences set NEXTVAL = (@maxRecId + 1) where TABID = @tableID; --Increase nextValue = maxRecId + 1
print 'Updated nextValue, ' + cast(@tableId as varchar(10)) + ', ' + @tableName + ', from: ' + cast(@nextVal as varchar(50)) + ', to: ' + cast((@maxRecId + 1) as varchar(50));
end
fetch next from @db_cursor into @tableID, @tableName, @nextVal;
End
CLOSE @db_cursor;
DEALLOCATE @db_cursor;
Print 'Checking is completed';
Hi Adam, i have done all the setps u suggested but no luck. can suggest me a fix.?
ReplyDelete