Retour
-
Titre
Cannot insert duplicate key in object 'dbo.HistoryJob' -
Description
After upgrading the History database, the following error is seen:
"(execute slot single)50000 0 re-throw in Procedure HDB_ZProcessGroup, Line 450000 0 re-throw in Procedure HDB_PProcessGroup, Line 450000 0 re-throw in Procedure HDB_PProcessGroup_i, Line 682627 0 detected in (SRV=servername) Procedure HDB_PProcessGroup_i, Line 302627 0 Violation of PRIMARY KEY constraint 'PK__HistoryJ__789A82F15EAF6584'. Cannot insert duplicate key in object 'dbo.HistoryJob'. The duplicate key value is (####)." -
Cause
There are duplicates in the history database. -
Résolution
1. Execute the following on the Identity Manager database to confirm that there are duplicate entries in JobHistory:
select * from JobHistory
where UID_Job in (select UID_JOb from JobHistory group by UID_Job having count(*) > 1)
and UID_JobHistory not in (select max(UID_JobHistory) from JobHistory group by UID_Job having count(*) > 1)
2. If there are duplicates execute the following to delete them:
delete JobHistory
where UID_Job in (select UID_JOb from JobHistory group by UID_Job having count(*) > 1)
and UID_JobHistory not in (select max(UID_JobHistory) from JobHistory group by UID_Job having count(*) > 1)
3. Then execute the following on the History DB to confirm that there are duplicate entries in RawJobHistory:
select * from RawJobHistory
where UID_Job in (select UID_JOb from RawJobHistory group by UID_Job having count(*) > 1)
and UID_JobHistory not in (select max(UID_JobHistory) from RawJobHistory group by UID_Job having count(*) > 1)
4. If there are duplicates execute the following to delete them (please ensure a recent database backup exists):
delete RawJobHistory
where UID_Job in (select UID_JOb from RawJobHistory group by UID_Job having count(*) > 1)
and UID_JobHistory not in (select max(UID_JobHistory) from RawJobHistory group by UID_Job having count(*) > 1)