469,950 Members | 2,185 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Cannot insert duplicate key

1
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:
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4. ALTER procedure [dbo].[sp_InsertwhStorageUnit] (
  5.   @whStorageUnitCode character varying(40),
  6.   @whStorageUnitScanCode character varying(80),
  7.   @id_whStorageUnitKind smallint,
  8.   @whProductCode character varying(40),
  9.   @whProductDate datetime,
  10.   @whProductQuantity integer,
  11.   @whProductIndicator character varying(40),
  12.   @whProductBatchCode character varying(40),
  13.   @whStorageUnitWidth integer,
  14.   @whStorageUnitLength integer,
  15.   @whStorageUnitHeight integer,
  16.   @whStorageUnitWeight integer,
  17.   @id_whStorageUnit integer output) as
  18. set xact_abort on
  19.   insert into [whStorageUnit]
  20.     ([whStorageUnitCode], [whStorageUnitScanCode], [id_whStorageUnitKind], [whProductCode], [whProductDate], [whProductQuantity], [whProductIndicator], [whProductBatchCode], [whStorageUnitWidth], [whStorageUnitLength], [whStorageUnitHeight], [whStorageUnitWeight])
  21.   values
  22.     (@whStorageUnitCode, @whStorageUnitScanCode, @id_whStorageUnitKind, @whProductCode, @whProductDate, @whProductQuantity, @whProductIndicator, @whProductBatchCode, @whStorageUnitWidth, @whStorageUnitLength, @whStorageUnitHeight, @whStorageUnitWeight)
  23.   set nocount on
  24.   select @id_whStorageUnit = @@identity
Jan 18 '08 #1
1 2688
ck9663
2,878 Expert 2GB
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
Jan 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Jean-Christian Imbeault | last post: by
3 posts views Thread by Hai Nguyen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.