Violation of Primary Key constraint. Cannot insert duplicate key in object error when using Item List
Today, I have another blog post based on a recent support case.
The partner logged a case with us where the customer was receiving the error "Violation of Primary Key constraint. Cannot insert duplicate key in object" when they used the Item List in Microsoft Dynamics GP.
The table referenced in the error was a temporary table. Below is the screenshot of the error:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY constraint 'PK##1955155'. Cannot insert duplicate key in object 'dbo.##1955155'.
I logged on my system and found that the list data is populated from by the IV_ItemList stored procedure.
BEGIN DECLARE @stored_proc_name char(19) DECLARE @retstat int DECLARE @param2 int set nocount on SELECT @stored_proc_name = 'TWO.dbo.IV_ItemList' EXEC @retstat = @stored_proc_name '##0602745', @param2 OUT SELECT @retstat, @param2 set nocount on END
Looking at the stored procedure it is getting its data from IV00101 and IV00102 using the following select statement to populate the table.
exec(' select IV00101.ITEMNMBR,IV00101.ITMCLSCD,IV00101.ITEMDESC,IV00101.ITEMTYPE,IV00101.DECPLQTY,IV40201.BASEUOFM,'
+ ' IV00101.ITMSHNAM, IV00101.ITMGEDSC,IV00101.LOCNCODE,'
+ ' (ISNULL(IV00102.QTYONHND,0.00000) - ISNULL(IV00102.ATYALLOC,0.00000)) - ISNULL(IV00102.QTYCOMTD,0.00000), '
+ ' IV00101.USCATVLS_1,IV00101.USCATVLS_2,IV00101.USCATVLS_3,IV00101.USCATVLS_4,IV00101.USCATVLS_5,IV00101.USCATVLS_6,'
+ ' IV00102.ATYALLOC, IV00102.QTYONHND, IV00102.QTYONORD,' + ' 0,0'
+ ' FROM IV00101 LEFT OUTER JOIN IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR AND IV00101.MSTRCDTY = IV00102.RCRDTYPE '
+ ' LEFT OUTER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDL')
I modified the query so we can look for duplicates in the data set
exec('select ITEMNMBR from (select IV00101.ITEMNMBR,IV00101.ITMCLSCD,IV00101.ITEMDESC,IV00101.ITEMTYPE,IV00101.DECPLQTY,IV40201.BASEUOFM,'
+ ' IV00101.ITMSHNAM, IV00101.ITMGEDSC,IV00101.LOCNCODE,'
+ ' (ISNULL(IV00102.QTYONHND,0.00000) - ISNULL(IV00102.ATYALLOC,0.00000)) - ISNULL(IV00102.QTYCOMTD,0.00000) as c10, '
+ ' IV00101.USCATVLS_1,IV00101.USCATVLS_2,IV00101.USCATVLS_3,IV00101.USCATVLS_4,IV00101.USCATVLS_5,IV00101.USCATVLS_6,'
+ ' IV00102.ATYALLOC, IV00102.QTYONHND, IV00102.QTYONORD,' + ' 0 as c20,0 as c21'
+ ' FROM IV00101 LEFT OUTER JOIN IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR AND IV00101.MSTRCDTY = IV00102.RCRDTYPE '
+ ' LEFT OUTER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDL) A group by ITEMNMBR having count(*) > 1')
With a little more research, we were able to confirm that there were duplicate records in the IV_Item_MSTR_QTYS (IV00102) table. These duplicates in turn caused the temporary table used by the Item List query to have duplicates and generate the error.
The questions had to be asked "How could the table get duplicate records in it?". "Aren't the indexes meant to stop duplicate records from being created?".
Well, using SQL Server Management Studio we looked at the indexes on the table to find that there were none defined, they were missing!
Before we could successfully recreate the indexes, we needed to locate and remote the duplicates.
The IV00102 table should have 4 indexes of which the first 3 do not allow duplicates. The key fields for the 3 indexes are:
- ITEMNMBR, RCRDTYPE, LOCNCODE
- LOCNCODE, ITEMNMBR
- PRIMVNDR, ITEMNMBR, LOCNCODE
I created the following queries to look for duplicate records for the 3 indexes.
Scripts to locate Duplicates for 3 indexes
-- Identify duplicates for Key 1
select I.ITEMNMBR, I.RCRDTYPE, I.LOCNCODE, I.DEX_ROW_ID
from IV00102 I
join
(
select ITEMNMBR, RCRDTYPE, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by ITEMNMBR, RCRDTYPE, LOCNCODE
having count(*) > 1
) D on D.ITEMNMBR = I.ITEMNMBR and D.RCRDTYPE = I.RCRDTYPE and D.LOCNCODE = I.LOCNCODE
-- Identify duplicates for Key 2
select I.LOCNCODE, I.ITEMNMBR, I.DEX_ROW_ID
from IV00102 I
join
(
select LOCNCODE, ITEMNMBR, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by LOCNCODE, ITEMNMBR
having count(*) > 1
) D on D.LOCNCODE = I.LOCNCODE and D.ITEMNMBR = I.ITEMNMBR
-- Identify duplicates for Key 3
select I.PRIMVNDR, I.ITEMNMBR, I.LOCNCODE, I.DEX_ROW_ID
from IV00102 I
join
(
select PRIMVNDR, ITEMNMBR, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by PRIMVNDR, ITEMNMBR, LOCNCODE
having count(*) > 1
) D on D.PRIMVNDR = I.PRIMVNDR and D.ITEMNMBR = I.ITEMNMBR and D.LOCNCODE = I.LOCNCODE
Then I used the DEX_ROW_ID identity column to identify the original (first created) record, so we could keep that one and remove the rest of the duplicates. The following queries show the records which will be deleted for the 3 indexes.
Scripts to locate the records to remove for 3 indexes
-- Identify records to remove for Key 1 Duplicates
select I.ITEMNMBR, I.RCRDTYPE, I.LOCNCODE, I.DEX_ROW_ID
from IV00102 I
join
(
select ITEMNMBR, RCRDTYPE, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by ITEMNMBR, RCRDTYPE, LOCNCODE
having count(*) > 1
) D on D.ITEMNMBR = I.ITEMNMBR and D.RCRDTYPE = I.RCRDTYPE and D.LOCNCODE = I.LOCNCODE
where I.DEX_ROW_ID <> D.DEX_ROW_ID
-- Identify records to remove for Key 2 Duplicates
select I.LOCNCODE, I.ITEMNMBR, I.DEX_ROW_ID
from IV00102 I
join
(
select LOCNCODE, ITEMNMBR, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by LOCNCODE, ITEMNMBR
having count(*) > 1
) D on D.LOCNCODE = I.LOCNCODE and D.ITEMNMBR = I.ITEMNMBR
where I.DEX_ROW_ID <> D.DEX_ROW_ID
-- Identify records to remove for Key 3 Duplicates
select I.PRIMVNDR, I.ITEMNMBR, I.LOCNCODE, I.DEX_ROW_ID
from IV00102 I
join
(
select PRIMVNDR, ITEMNMBR, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by PRIMVNDR, ITEMNMBR, LOCNCODE
having count(*) > 1
) D on D.PRIMVNDR = I.PRIMVNDR and D.ITEMNMBR = I.ITEMNMBR and D.LOCNCODE = I.LOCNCODE
where I.DEX_ROW_ID <> D.DEX_ROW_ID
Once the duplicate records had been identified we could remove them to leave just a single record: Below are the scripts to remove the duplicates.
Script to remove duplicate records for 3 indexes
-- Remove records for Key 1 Duplicates
delete I
from IV00102 I
join
(
select ITEMNMBR, RCRDTYPE, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by ITEMNMBR, RCRDTYPE, LOCNCODE
having count(*) > 1
) D on D.ITEMNMBR = I.ITEMNMBR and D.RCRDTYPE = I.RCRDTYPE and D.LOCNCODE = I.LOCNCODE
where I.DEX_ROW_ID <> D.DEX_ROW_ID
-- Remove records for Key 2 Duplicates
delete I
from IV00102 I
join
(
select LOCNCODE, ITEMNMBR, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by LOCNCODE, ITEMNMBR
having count(*) > 1
) D on D.LOCNCODE = I.LOCNCODE and D.ITEMNMBR = I.ITEMNMBR
where I.DEX_ROW_ID <> D.DEX_ROW_ID
-- Remove records for Key 3 Duplicates
delete I
from IV00102 I
join
(
select PRIMVNDR, ITEMNMBR, LOCNCODE, count(0) as [COUNT], min(DEX_ROW_ID) as DEX_ROW_ID
from IV00102
group by PRIMVNDR, ITEMNMBR, LOCNCODE
having count(*) > 1
) D on D.PRIMVNDR = I.PRIMVNDR and D.ITEMNMBR = I.ITEMNMBR and D.LOCNCODE = I.LOCNCODE
where I.DEX_ROW_ID <> D.DEX_ROW_ID
So now we had cleaned up the data, the best/simplest way to get the indexes and the associated zDP stored procedures created was to backup the data, recreate the table using the SQL Maintenance feature from inside Microsoft Dynamics GP and then copy the data back. The following blog post provides the method you can use to perform these steps:
So the partner completed the steps and tested the system and it still allowed new duplicate records to be created. The only way this could happen is if the indexes were not created properly. So I asked the partner to check that they existed by looking in SQL Server Management Studio. The screenshot below is how it should look:
The only reason that I could think of for the index creation to fail is if indexes of the same name already existed. So I asked the partner to also see if the indexes could be found elsewhere. He came back with the following screenshot:
As you can see the indexes on the IV00102 were missing, but there was a backup copy of the table with the same index names as the indexes we were trying to create.
I requested the partner remove those indexes and go through the backup/recreate/restore process again and this time the indexes were created and the system no longer created duplicate records and without the duplicates the Item List worked properly.
So the moral of this story: Do not rename existing tables as a backup of a table as this will leave indexes in place which will prevent the recreation of the indexes when the table is recreated.
If you do rename the table, then rename the indexes to match or remove the indexes. Better still you can use SELECT * INTO <BackupTable> FROM <Table> instead, to copy the data to a new table
I hope you find this information useful.
David