Error message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error message

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2013-11-01 : 10:50:07

I am doing a simple insert and calling a procedure shown below.

I am getting the following message/error

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'STK_ALLOCATION_TEMP_PK'. Cannot insert duplicate key in object 'dbo.STK_ALLOCATION_TEMP'. The duplicate key value is (1, 1, 0).

I would like to know how I can catch this error and put it into a table. Is this possible?

I would be glad of any help.

Insert into STK_ALLOCATION_TEMP

(STKAL_ID -- Transaction ID. Used to group transaction detail lines together. Unique and auto generated in Dimensions. See the section on Usage for more details
,STKAL_LINE_NO -- Stock allocation line number. See the section on Validation.
,STKAL_ORDER_DETAIL_LINK -- Unique identifier for the ordered stock line Table: ORD_DETAIL, Field: OD_PRIMARY
,STKAL_QUANTITY -- Amount of stock allocated to this line. See the section on Usage for more details
,STKAL_USER_ID -- ID of the user populating this table
,STKAL_MULTI_SUBANALYSIS_LINK -- Link to STK_SUBANALYSIS_SERIAL_TEMP , STKSUBSNO_ID field
,STKAL_SERIAL_NUMBER
,STKAL_ORDER_TYPE) -- ‘S’ = Sales Order, ‘W’ = Works Orders

Values

(1 -- STKAL_ID Transaction ID. Used to group transaction detail lines together. Unique and auto generated in Dimensions. See the section on Usage for more details
,1 -- STKAL_LINE_NO Stock allocation line number. See the section on Validation.
,9 -- STKAL_ORDER_DETAIL_LINK Unique identifier for the ordered stock line Table: ORD_DETAIL, Field: OD_PRIMARY
,-2 -- STKAL_QUANTITY Amount of stock allocated to this line. See the section on Usage for more details
,'sa' -- STKAL_USER_ID ID of the user populating this table
,1 --STKAL_MULTI_SUBANALYSIS_LINK
,'<multiple>'
,'S') -- STKAL_ORDER_TYPE ‘S’ = Sales Order, ‘W’ = Works Orders

exec AA_STK_ALLOCATION_POST_S
@PS_TRN_TEMP_ID=1 -- Use of this parameter needs care when using. See the section on Usage for more details.
,@PS_USER_ID='SA' -- ID of user calling procedure. Must be a valid user within Dimensions with the correct permissions via their security profiles.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-01 : 11:17:53

Test if there are existing rows that would violate the pK constraint before you insert the data - for example like this:

INSERT INTO YourLogTable
SELECT * FROM STK_ALLOCATION_TEMP t WHERE 
yourpkcolumn1 = 1 AND yourpkcolumn2 = 1 AND your pkcolumn3 = 0

IF (@@rowcount = 0)
Insert into STK_ALLOCATION_TEMP
... REst of your query here

This has a slight possibility that if there are several clients trying to insert data into the table, it can still cause a problem. If that is a consideration, post back.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2013-11-02 : 07:16:23

Thanks,

I have found that the following works

EXEC dbo.Aa_stk_allocation_post_s
@PS_TRN_TEMP_ID = @stkal_id -- Use of this parameter needs care when using. See the section on Usage for more details.
,
@PS_USER_ID = 'WMS' -- ID of user calling procedure. Must be a valid user within Dimensions with the correct permissions via their security profiles.
,
@PS_Proccess_Status = @Proccess_Status out

IF @Proccess_Status <> 0
BEGIN
INSERT INTO UDEF_DISPATCHER_ERROR_LOG_STK_ALLOCATION_TEMP
SELECT
TOP 100 PERCENT
[STKAL_ID]
,[STKAL_LINE_NO]
,[STKAL_SUB_LINE_NO]
,[STKAL_STATUS]
,[STKAL_ORDER_DETAIL_LINK]
,[STKAL_QUANTITY]
,[STKAL_USER_ID]
,[STKAL_SUBANALYSIS]
,[STKAL_MULTI_SUBANALYSIS_LINK]
,[STKAL_SERIAL_NUMBER]
,[STKAL_CUSTOMER_CODE]
,[STKAL_ORDER_NUMBER]
,[STKAL_REQUIRED_DATE]
,[STKAL_QUANTITY_TO_DELIVER]
,[STKAL_QUANTITY_ALLOCATED]
,[STKAL_QUANTITY_OS_TO_ALLOCATE]
,[STKAL_ORDER_TYPE]
,( CASE
WHEN [STKAL_STATUS] = 0 THEN 'No error.'
WHEN [STKAL_STATUS] = 1 THEN 'Order detail does not exist.'
WHEN [STKAL_STATUS] = 2 THEN 'Order Detail no longer requires allocation.'
WHEN [STKAL_STATUS] = 3 THEN 'Over allocation or de-allocation attempted.'
WHEN [STKAL_STATUS] = 4 THEN 'Not currently used.'
WHEN [STKAL_STATUS] = 5 THEN 'Serial number already assigned.'
WHEN [STKAL_STATUS] = 6 THEN 'Not enough stock.'
WHEN [STKAL_STATUS] = 7 THEN 'Serial number not available.'
WHEN [STKAL_STATUS] = 8 THEN 'Serial number does not exist.'
WHEN [STKAL_STATUS] = 9 THEN 'Sub analysis is no longer permitted.'
WHEN [STKAL_STATUS] = 10 THEN 'Sub analysis does not exist.'
WHEN [STKAL_STATUS] = 11 THEN 'could not allocate as no sub analysis was specified.'
WHEN [STKAL_STATUS] = 12 THEN 'Total multiple quantities do not match detail line quantity.'
WHEN [STKAL_STATUS] = 13 THEN 'Unspecified error.'
WHEN [STKAL_STATUS] = 14 THEN 'Serial number does not belong to the specified sub analysis.'
WHEN [STKAL_STATUS] = 15 THEN 'Security level access to stock denied.'
WHEN [STKAL_STATUS] = 16 THEN 'Security level access to stock sub analysis denied.'
WHEN [STKAL_STATUS] = 17 THEN 'Unable to find an inward movement to get a FIFO cost price from.'
WHEN [STKAL_STATUS] = 18 THEN 'cannot allocate stock to a Works Order that is closed, merged or complete.'
WHEN [STKAL_STATUS] = 19 THEN 'cannot de-allocate from a sub-analysis that the order was not allocated to.'
ELSE ''
END )
,Getdate()
FROM [STK_ALLOCATION_TEMP]
WHERE
Isnull(STKAL_STATUS,
'') <> 0
AND [STKAL_ID] = @stkal_id

--select ERROR_STKAL_status,* from STK_ALLOCATION_TEMP
DELETE FROM STK_ALLOCATION_TEMP
WHERE STKAL_ID = @stkal_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources