SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error message
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle Doouss
Yak Posting Veteran

United Kingdom
50 Posts

Posted - 11/01/2013 :  10:50:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 11/01/2013 :  11:17:53  Show Profile  Reply with Quote
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.
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
50 Posts

Posted - 11/02/2013 :  07:16:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000