Published On: January 24, 2024Categories: Technical

If you have an issue with a duplicate value for RecId field in some table you can fix it with executing a script/procedure in SQL.

Example:

Considering below example query as where duplicate errors are appearing.

INSERT INTO WMSLocationAggregateStatus results in duplicate key error.

You will need to check what keys the relevant tables have and determine what the likely duplicate column is. In this particular case it is related to a system sequence issue.

The error is happening because the next RecId that is generated for a new record is already used in another record. We all know that RecId should be unique for every record in the table.

For issues related to the RecId sequence, the sequence name will be table specific. You can use the following query to determine what the correct RecId sequence name is for your table:

Query: SELECT ID FROM TABLEIDTABLE WHERE NAME LIKE /*<Table Name as a string>*/;

The RecId sequence name for the table will be “SEQ_” followed by whatever Id is returned by the query. For example, if the above returned 12345, then the RecId sequence name will be “SEQ_12345”.

Example:SELECT ID FROM TABLEIDTABLE WHERE NAME LIKE WMSLocationAggregateStatus;   –19972

select max(RecID) from WMSLocationAggregateStatus

–5637173928

select current_value from sys.sequences where name = ‘SEQ_19972’

–5637164075

If current_value from sys.sequences is lower than the max(RecID) from the table then you need to correct this.

Example script will be as below:

DECLARE @SequenceName varchar;SET @SequenceName = ‘SEQ_19972’;

DECLARE @SafeRange int;

SET @SafeRange = 1000;

DECLARE @CurrentValueFromSequence bigint;

DECLARE @CurrentValueFromTable bigint;

DECLARE @NewSequenceValue bigint;

DECLARE @Discard bigint;

BEGIN TRANSACTION;

SELECT @CurrentValueFromSequence = CAST(seq.current_value AS bigint) FROM sys.sequences seq WHERE seq.name = @SequenceName;

SELECT TOP 1@CurrentValueFromTable = RecId FROM WMSLocationAggregateStatus ORDER BY RecId DESC;

IF @CurrentValueFromTable >=@CurrentValueFromSequence

BEGIN

SET@NewSequenceValue=(@CurrentValueFromTable -@CurrentValueFromSequence)+@SafeRange

EXEC sp_sequence_get_range @SequenceName, @NewSequenceValue, @Discard;

END

COMMIT TRANSACTION;

Template script is:

You need to fill the yellow fields to run the script for your needs.

Script:

DECLARE @SequenceName varchar;

SET @SequenceName = /*<Sequence Name>*/;

DECLARE @SafeRange int;

SET @SafeRange = 1000;

DECLARE @CurrentValueFromSequence bigint;

DECLARE @CurrentValueFromTable bigint;

DECLARE @NewSequenceValue bigint;

DECLARE @Discard bigint;

BEGIN TRANSACTION;

SELECT @CurrentValueFromSequence = CAST(seq.current_value AS bigint) FROM sys.sequences seq WHERE seq.name = @SequenceName;

SELECT TOP 1 @CurrentValueFromTable = /*<Column Name>*/ FROM /*<Table Name<*/ ORDER BY /*<Column Name>*/ DESC;

IF @CurrentValueFromTable >= @CurrentValueFromSequence

BEGIN

SET@NewSequenceValue=(@CurrentValueFromTable-@CurrentValueFromSequence)+@SafeRange

EXEC sp_sequence_get_range @SequenceName,@NewSequenceValue, @Discard;

END

COMMIT TRANSACTION;

What to do if the script is not working:

If the script is not working you can run the following line

exec sp_sequence_get_range [ @sequence_name = ] N'<sequence>’, [ @range_size = ] range_size,[ @range_first_value = ]range_firstValueOutput

Note that in @range_size you need to enter a number by how many you want to increase the current_value for that sequence.
Best way to determinate this is if you get the max(RecId) from that table sustract the current_value for that sequence and add 1000 to it. This way you will be sure that the RecId is not used and you wont have a conflict.

Example
select max(RecID) from WMSLocationAggregateStatus  result –5637173928

select current_value from sys.sequences where name = ‘SEQ_19972’; result –5637164075

Range size = 5637173928 – 5637164075 + 1000 = 10,853exec sp_sequence_get_range SEQ_19972, @range_size = 10853, @range_first_value = 1

How to run the Procedure:

For cloud hosted environments you can do it directly in SQL Server Management Studio on the environment itself.
For Microsoft managed environments that are not PROD you can connect using a JIT access and run the script/procedure.
For PROD environment you need to contact Microsoft to do that for you.