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.