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
>

Primary Key Violation - Transactional Replication

Hi All,
I have setup a transactional replication between 2 SQL 2005 servers.
Unfortunately, I am getting the error listed below:
Replication-Replication Distribution Subsystem: agent
JFCIS3TRM02-JFJDAT-JFJDAT_PUBLICATION-JFCIS3TRM01-13 failed.
Violation of PRIMARY KEY constraint 'ARSTRUN_KEY_0'. Cannot insert duplicate
key in object 'dbo.ARSTRUN'.
Is this error being raised because I haven't enabled automatic range
management? If so, how would I fix this error?
Regards,
JN
Hi Paul,
The type of transactional replication setup is just the plain one, not the
one with updatable subscription.
I am not sure whether it is nosync or automatic as I used the wizard and I
didn't recall being asked for those settings.
Regards,
JN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWzOLQD2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>I really need to know what type of transactional replication setup you have
>configured - plain, updatable (immediate or queued) and nosync or
>automatic...
> Cheers,
> Paul Ibison
>
|||That could be possible since I have created an ODBC connection to the
replicated database which an end user can select from a drop down list when
they open the application.
What do you suggest I do to get the two databases synchronized again?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OjdWiMF2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> OK - in that case my suspicion is that someone has entered a row on the
> subscriber. Is that possible? In this plain transactional case the
> subscriber data is supposed to be read only and only changed via the
> distribution agent.
> HTH,
> Paul Ibison
>