Share
ReportHi everyone,
Error: Cannot create a record in History of Allocated Numbers (CustomTableName). Customer account: 12345, 12345678. The record already exists.
when i insert the record into customtable the above error is showing because recid already exist which is same as the next value to be generated by system sequence table,so i updated the system sequence table nextvalue and gave full database sync but it still showing error . Please advice me if any other reason exist.
here is my update query:
DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT
SELECT @MaxRecID = MAX(RECID)
FROM CustomTableName
SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'CustomTableName'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
IF (@NextVal > @MaxRecID)
BEGIN
PRINT 'CustomTableNamedid not need to be updated.'
END
ELSE
BEGIN
PRINT 'Updated CustomTableName from ' + CONVERT(VARCHAR(MAX), @NextVal) + '' to '' + CONVERT(VARCHAR(MAX), @MaxRecID + 1)
UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'CustomTableName'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
END
*This post is locked for comments
All responses (
Answers (
1,657
