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?

-----------------------------------------------------
WMSImportConsumer(CDMSImportConsumer): Error 'Violation of UNIQUE KEY constraint 'UQ__whStorageUnit__6BCEF5F8'. Cannot insert duplicate key in object 'dbo.whStorageUnit'' when attempting to execute stored proc { call [sp_InsertwhStorageUnit](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) } Line: 3 Column: 0-Message: <?xml version="1.0" encoding="ISO-8859-1"?>
<comtec Version="1.0"><StorageUnit><ScanCode>3541332137009 30275</ScanCode><StorageUnitID>176167</StorageUnitID><ProductCode>05410003242225</ProductCode><ProductDate>2008-10-12</ProductDate><ProductQuantity>56</ProductQuantity><StorageUnitKind>Full</StorageUnitKind><Indicator>A</Indicator></StorageUnit></comtec>
.

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

and the stored procedure is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_InsertwhStorageUnit] (
  @whStorageUnitCode character varying(40),
  @whStorageUnitScanCode character varying(80),
  @id_whStorageUnitKind smallint,
  @whProductCode character varying(40),
  @whProductDate datetime,
  @whProductQuantity integer,
  @whProductIndicator character varying(40),
  @whProductBatchCode character varying(40),
  @whStorageUnitWidth integer,
  @whStorageUnitLength integer,
  @whStorageUnitHeight integer,
  @whStorageUnitWeight integer,
  @id_whStorageUnit integer output) as
set xact_abort on
  insert into [whStorageUnit]
    ([whStorageUnitCode], [whStorageUnitScanCode], [id_whStorageUnitKind], [whProductCode], [whProductDate], [whProductQuantity], [whProductIndicator], [whProductBatchCode], [whStorageUnitWidth], [whStorageUnitLength], [whStorageUnitHeight], [whStorageUnitWeight])
  values
    (@whStorageUnitCode, @whStorageUnitScanCode, @id_whStorageUnitKind, @whProductCode, @whProductDate, @whProductQuantity, @whProductIndicator, @whProductBatchCode, @whStorageUnitWidth, @whStorageUnitLength, @whStorageUnitHeight, @whStorageUnitWeight)
  set nocount on
  select @id_whStorageUnit = @@identity

[ck9663]

Hello,
...........................
...................
set nocount on
select @id_whStorageUnit = @@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