Primary Key Violation Constraint, how to debug.....

Hi all,
I have a stored procedure (from a vendor) that attempts to insert some
records.. Unfortunately, its a very buggy early version, and tech support is
sketchy at best, so I'm trying to figure out the problem myself..
This is the error I'm getting:
Server: Msg 2627, Level 14, State 1, Procedure
usp_SAIncShipToDeliveryLocation, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
object 'SA_ShipToDeliveryLocation'.
This is the code fragment that is causing the problem. I'd like to identify
the specific record that is causing primary key violation.. Is this
possible?
insert into SA_ShipToDeliveryLocation
(ShipToDeliveryLocationID
,ShipToDeliveryLocationName
,ShipToDeliveryLocationState
,ShipToDeliveryLocationZip
,ShipToDeliveryLocationCountry
,RegionKey
,CustomerKey
,ShipToDeliveryLocationKey
)
select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
when 0
then r.RegionKey
else null
end RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
from #SASTemp_ShipToDeliveryLocation sd
left join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A'
order by c.CustomerKey
,sd.ShipToDeliveryLocationID
set @.nError = @.@.error
if (@.nError <> 0)
begin
rollback tran;
return @.nError;
endTake your select statment, remove all columns but those in the PK column (or
columns), group by these columns and add a having clause (having count(*) >
1). Off hand, I think the use of a cross join is suspicious (unless there
is only one row in the table). Another thing to check is a poorly written
insert trigger (but that should generate an error with the trigger name in
it).|||There are 2 possible causes for this error. Either a row with the PK value
already exists in the target table or the select statement is returning more
that one row with the same key.
Assuming ShipToDeliveryLocationKey is the primary key of
SA_ShipToDeliveryLocation, you can include your source query as a derived
table to easily identify problem data. See untested examples below.
The CROSS JOIN looks suspect here since this will effectively multiply the
number or rows returned. You'll get the PK error if the
SA_ControlParameters table contains more than one row.
--keys that already exist
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
WHERE EXISTS
(
SELECT *
FROM SA_ShipToDeliveryLocation target
WHERE target.ShipToDeliveryLocationKey =
source.ShipToDeliveryLocationKey
)
--keys that duplicated in source query
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
JOIN
(SELECT sd.ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r ON sd.RegionID = r.RegionID
ANDr.RegionType = 'R'
JOIN SA_Customer c ON c.CustomerKey = sd.CustomerKey
CROSS JOIN SA_ControlParameters cp
WHERE sd.AddChangeDelete = 'A'
GROUP BY sd.ShipToDeliveryLocationKey
HAVING COUNT(*) > 1) dups ON
dups.ShipToDeliveryLocationKey = source.ShipToDeliveryLocationKey
Hope this helps.
Dan Guzman
SQL Server MVP
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>|||Thanks for the advice guys. Dan I'll give the derived table a shot and get
back to
Thanks very much
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>