Cannot insert duplicate key

[BenB]

Hello,

A try:
I have got an application which runs smoothly under Windows 2003 server with SQLserver 2005. No problems at all...
The database is installed on the same server as the application.

We are working on redundancy, so we have installed this application under VMWare. This VMWare is running at a dedicated VMWare hardware.
In the VMWare, the rest is (or should be) similar: windows 2003 server, MSSQL server 2005, the application...
The application is inserting new pallet in a databases. A new pallet is introduced by 2 messages:
Message 1: SSCC, pallet ID, and inserts details like product
Message 2: SSCC, pallet ID, inserts details like weight
SSCC and palletID should be both unique.
The order in which the messages arrive is not important. I have tested this..

When message 1 arrives, it inserts the record and message 2 add's information.
And Vice versa...
message 1 is send via xml files, transferred internally to MSqueue and inserted by the application in MSSQL
message 1 is send via MSqueue, transferred internally to MSqueue and inserted by the application in MSSQL

We have only one entry point, so new pallets are coming after each other with a delay of 30s.


However (otherwise I wasn't posting here :-) ), there is "sometimes" a problem.
It runs smoothly for say a 100 pallets, and suddenly I receive this message for a new pallet...

We have been searching and testing for some weeks now, but we still haven't found any clue. Does anybody have any idea what can cause this?

-----------------------------------------------------
WMSImportConsum er(CDMSImportCo nsumer): Error 'Violation of UNIQUE KEY constraint 'UQ__whStorageU nit__6BCEF5F8'. Cannot insert duplicate key in object 'dbo.whStorageU nit'' when attempting to execute stored proc { call [sp_InsertwhStor ageUnit](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) } Line: 3 Column: 0-Message: <?xml version="1.0" encoding="ISO-8859-1"?>
<comtec Version="1.0">< StorageUnit><Sc anCode>35413321 3700930275</ScanCode><Stora geUnitID>176167 </StorageUnitID>< ProductCode>054 10003242225</ProductCode><Pr oductDate>2008-10-12</ProductDate><Pr oductQuantity>5 6</ProductQuantity ><StorageUnitKi nd>Full</StorageUnitKind ><Indicator>A </Indicator></StorageUnit></comtec>
.

-----------------------------------------------------

and the stored procedure is:
[code=sql]
set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER procedure [dbo].[sp_InsertwhStor ageUnit] (
@whStorageUnitC ode character varying(40),
@whStorageUnitS canCode character varying(80),
@id_whStorageUn itKind smallint,
@whProductCode character varying(40),
@whProductDate datetime,
@whProductQuant ity integer,
@whProductIndic ator character varying(40),
@whProductBatch Code character varying(40),
@whStorageUnitW idth integer,
@whStorageUnitL ength integer,
@whStorageUnitH eight integer,
@whStorageUnitW eight integer,
@id_whStorageUn it integer output) as
set xact_abort on
insert into [whStorageUnit]
([whStorageUnitCo de], [whStorageUnitSc anCode], [id_whStorageUni tKind], [whProductCode], [whProductDate], [whProductQuanti ty], [whProductIndica tor], [whProductBatchC ode], [whStorageUnitWi dth], [whStorageUnitLe ngth], [whStorageUnitHe ight], [whStorageUnitWe ight])
values
(@whStorageUnit Code, @whStorageUnitS canCode, @id_whStorageUn itKind, @whProductCode, @whProductDate, @whProductQuant ity, @whProductIndic ator, @whProductBatch Code, @whStorageUnitW idth, @whStorageUnitL ength, @whStorageUnitH eight, @whStorageUnitW eight)
set nocount on
select @id_whStorageUn it = @@identity[/code]

[ck9663]

Hello,
............... ............
............... ....
set nocount on
select @id_whStorageUn it = @@identity

1. check your primary key
2. check the value of those fields that you're inserting
3. check if those values are already existing in db.
4. resolve if you really need to insert duplicate record (you might need a diffirent eky) or analyze your input...
5. most importantly, use the code tag when posting ;)

-- CK