Store Procedure in ADF PK Violation

Mahlaule, Rirhadzu 0

Stored Procedure in ADF it is generating “PK violation” runtime error.

Nandan Hegde 32,026 Reputation points MVP

2024-09-06T11:20:01.7033333+00:00

Can you plz confirm what you stored procedure is doing? Is it copying/inserting data into another table?
Mahlaule, Rirhadzu 0 Reputation points

2024-09-08T12:02:45.75+00:00

Inserting data into another table
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-09-09T22:05:53.62+00:00

Hi @Mahlaule, Rirhadzu

Thank you for your question. A "PK violation" error typically occurs when you are trying to insert a record into a table that violates the primary key constraint of that table. This can happen if you are trying to insert a record with a primary key value that already exists in the table.

To troubleshoot this issue, you can try the following steps:

Check the primary key constraint of the table where you are trying to insert data. Make sure that the primary key column(s) are unique and that there are no duplicate values.

Check the data that you are trying to insert. Make sure that the primary key value(s) you are trying to insert do not already exist in the table.

Check the stored procedure that you are using to insert data. Make sure that the procedure is correctly written and that it is not inserting duplicate primary key values.

Check the source of the data that you are inserting. Make sure that there are no issues with the data source that could be causing duplicate primary key values.

If you are still experiencing issues after checking these steps, you may need to provide more information about your stored procedure and the data you are trying to insert in order to troubleshoot the issue further.

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-09-10T21:13:17.77+00:00

@Mahlaule, Rirhadzu Following up to see if the abovesuggestion was helpful. And, if you have any further query do let us know.

1 answer

Rirhandzu Mahlaule 0 Reputation points

2024-09-11T10:14:16.82+00:00

@Smaran Thoomu my stored Procedure Query deletes all the records from the destination table that matches the Aggregation from the temp table, which i believe that it ensures that no duplication records will exists in the destination table for the same PK id , and records are deleted only if they match the corresponding rows in the Temp table. Insert is used to Insert the new Aggregated data from Temp table into the destination table. however i still get PK Violation error
Please sign in to rate this answer.
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-09-12T15:30:39.7866667+00:00

Hi @Rirhandzu Mahlaule
Based on the additional information you provided, it seems that you are already taking the necessary steps to prevent duplicate records in the destination table. However, you are still experiencing a primary key violation error.

In this case, you may want to review the stored procedure and the data being inserted to ensure that there are no issues with the logic or the data that could be causing the error. You may also want to consider using a try-catch block in the stored procedure to handle the primary key violation error and provide a more informative error message to help with troubleshooting.

If you are still experiencing issues after reviewing the stored procedure and the data, you may want to consider reaching out to Microsoft support for further assistance.

I hope this helps! Let me know if you have any further questions.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Store Procedure in ADF PK Violation

1 answer

Your answer