Cannot insert duplicate key row in object ‘dbo.XXXXXXX’ with unique index ‘XXXXXXXXXX’. The duplicate key value is (xxxx)
Symptoms
When completing an archive utilizing CDA, the following error is encountered:
Cannot insert duplicate key row in object ‘dbo.XXXXXXX’ with unique index ‘XXXXXXXXXX’. The duplicate key value is (xxxx).
Reason(s)
Generally, these errors indicate that items found in the Master table noted in the error message were either deleted or altered within the live database following a previous archive. This error is encountered if new items entered following these removals/alterations conflict with primary key columns in records that already exist in the archive database. As these tables are only transferred and not removed all data within the archive database should match records still within the live database.
Resolution
Before proceeding with any troubleshooting, the following should be completed following a failed archive (when any error is encountered):
- If only Transfer was selected, restore the archive database prior to the failed archive.
- If Transfer and Remove were selected, restore the archive and production database prior to the failed archive.
The following script will help identify all records with matching columns for the primary keys indicated in the error. If any of the other columns do not match the error indicated will occur during subsequent archives.
First, identify the columns within the key specified in the error:
- Run the following script
- NOTE: Replace XXXXXX with the appropriate table name
sp_helpindex XXXXXXX
Simply copy the columns specified within the index_keys column. These can be pasted within the declared values in the next script.
Next, the script below will need to be updated to include your originating (production) and destination (archive) database. As well, you will need to insert the appropriate table name that is identified in the error:
NOTE: All values that need to be updated are found within the top declares:
set nocount on
declare @columnname varchar (255)
declare @allcolumns varchar (8000)
declare @DB1 varchar (255) = 'ORIGINATING' --Replace this with the production database.
declare @DB2 varchar (255) = 'DESTINATION' --Replace this with the archive database.
declare @key varchar (255) = 'XXXXX' --Paste the columns obtained when using the sp_help script here.
declare @table varchar (255) = 'XXXXXXX' --Replace this with the table noted in the error.
declare @PKcolumns varchar (8000)
declare @columnindex int
declare @currenttable varchar (255)
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
USE TWO -- Replace this with your production database.
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
declare cTable cursor for
select o.name
from sysobjects o
where o.type = 'U'
and o.name like @table
--and o.name not like '%temp'
order by name
open cTable
fetch next from cTable into @currenttable
while (@@FETCH_STATUS <> -1)
begin
begin
select @columnindex = 1
select @columnname = ''
select @allcolumns = ''
select @PKcolumns = ''
while @columnindex <=(select count(name) from sys.columns where is_identity = 0 and object_id in(select id from sysobjects where name = @currenttable))
begin
select @columnname =(select name from sys.columns where is_identity = 0 and object_id in
(select id from sysobjects where name = @currenttable and column_id = @columnindex))
select @columnindex += 1
if (@columnname not in('DEX_ROW_ID', 'DEX_ROW_TS')) --These columns will not be included.
if (@columnindex <=(select count(name) from sys.columns where is_identity = 0 and object_id in(select id from sysobjects where name = @currenttable)))
select @allcolumns = concat(@allcolumns, @columnname, ', ')
else
select @allcolumns = concat(@allcolumns, @columnname)
--if (@columnname in (select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = (@key)))
--if (@columnname not IN (select top 1 COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = (@key) order by ORDINAL_POSITION desc))
--begin
select @PKcolumns = concat(@key, ', ')
--end
end
end
--print @currenttable
--print @allcolumns
--print @PKcolumns
--print ''
if (rtrim(@allcolumns) like '%,')
begin
select @allcolumns = left(@allcolumns, len(rtrim(@allcolumns)) - 1)
end
if (rtrim(@PKcolumns) like '%,')
begin
select @PKcolumns = left(@PKcolumns, len(rtrim(@PKcolumns)) - 1)
end
if len(@PKcolumns) = 0 select @PKcolumns = ''''''
print concat('print ''', @DB1, '..', @currenttable, '''')
print concat('select * from ', @DB1, '..', @currenttable, ' WHERE concat('''', ', @PKcolumns, ', '''') in (select concat('''', ', @PKcolumns, ', '''') from ', @DB2, '..', @currenttable, ') and concat('''', ', @allcolumns, ','''') not in (select concat('''', ', @allcolumns, ','''') from ', @DB2, '..', @currenttable, ')')
print concat('print ''', @DB2, '..', @currenttable, '''')
print concat('select * from ', @DB2, '..', @currenttable, ' WHERE concat('''', ', @PKcolumns, ', '''') in (select concat('''', ', @PKcolumns, ', '''') from ', @DB1, '..', @currenttable, ') and concat('''', ', @allcolumns, ','''') not in (select concat('''', ', @allcolumns, ','''') from ', @DB1, '..', @currenttable, ')')
print ''
fetch next from cTable into @currenttable
end
deallocate cTable
Once complete, the results can be copied and pasted into a new query. Running the resulting scripts will identify any records that exist in either company that would cause a duplicate key error on the constraint specified in the error.
If any records are identified, these must be either removed or altered in the same way that was completed within live. However, if the records are removed from the archive database, this will cause issues when attempting to report on or access previously archived transactions that were against the original master record.
Another alternative would be to create a new archive database to store all new historical transactions.
We have a blog posted in regards to the importance of maintain master records following an archive:
CATEGORIES:
TAGS: