If you get this error message after copying and pasting a record in Primavera, the next key value may be out of sequence. This may be due to an import issue.
Run this script to find the key errors.
PRINT '====================================================================='
PRINT 'create a script for key errors and show the max key values and next key values'
PRINT '====================================================================='
GO
DECLARE @S NVARCHAR(MAX) = ''
;WITH
key_table_list
AS
(
select key_name, key_seq_num, table_name = LEFT(key_name, CHARINDEX('_',key_name, 0)-1), column_name = RIGHT(key_name, LEN(key_name) - CHARINDEX('_',key_name, 0)) FROM NEXTKEY
)
,
single_script
AS
(
SELECT
[RowNumber]= ROW_NUMBER() OVER(ORDER BY tl.table_name)
, script = 'SELECT table_name =''' + tl.table_name + ''', max_key_seq_num = MAX(' + tl.column_name + '), key_seq_num = '
+ CAST(tl.key_seq_num AS VARCHAR) + ', KeyError = CASE WHEN MAX(' + tl.column_name + ') > ' + CAST(tl.key_seq_num AS VARCHAR)
+ ' THEN ''EXEC dbo.getnextkeys N''''' + tl.column_name + ''''', '' + CAST(MAX(' + tl.column_name + ') - ' + CAST(tl.key_seq_num AS VARCHAR) + ' + 1 AS VARCHAR) + '', @NewKeyStart OUTPUT'' ELSE NULL END FROM ' + tl.table_name
FROM
key_table_list tl
INNER JOIN sys.objects ob ON ob.name = tl.table_name
WHERE ob.type = 'U'
)
SELECT @S = @S + CASE WHEN [RowNumber] != 1 THEN ' UNION ' ELSE '' END + script
FROM
single_script
EXEC ('SELECT * FROM (' + @S+ ') AS se WHERE KeyError IS NOT NULL')
GO
table_name
max_key_seq_num
key_seq_num
KeyError
spidmap
539
100
EXEC dbo.getnextkeys N'spid', 440, @NewKeyStart OUTPUT
wbrscat
3
1
EXEC dbo.getnextkeys N'wbrs_cat_id', 3, @NewKeyStart OUTPUT
The 'key_seq_num' should be + 1 greater than the 'max_key_seq_num'. Use the script from 'KeyError' to update the tables as below.
PRINT '====================================================================='
PRINT 'update next key to number of missing increments '
PRINT '====================================================================='
GO
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @NewKeyStart AS INT
EXEC dbo.getnextkeys N'spid', 440, @NewKeyStart OUTPUT
EXEC dbo.getnextkeys N'wbrs_cat_id', 3, @NewKeyStart OUTPUT
ROLLBACK TRANSACTION
--COMMIT TRANSACTION
