question

rehaan avatar image
rehaan asked

Capture Primary key violation error and send an alert

If a primary key column gets a duplicate column from insert statement. How to capture it as an event and send email alert. Violation of PRIMARY KEY constraint 'PK_Transferdata'. Cannot insert duplicate key in object 'dbo.Transferdata'. The duplicate key value is (17600123).

primary-keyerror-handlingalerts
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rehaan avatar image rehaan commented ·

declare @batchSize int = 1000 INSERT INTO [dbo].[TransferData] ([PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,[NewReadingId]) SELECT top (@batchSize) [PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,newid() FROM [dbo].[TransferPacket] where Processed is null order by CreateDate This is my sql statement. As this inserts 1000 rows, how can add a if statement ? can you please suggest and help with some sql

0 Likes 0 ·

1 Answer

·
WRBI avatar image
WRBI answered

Hi @rehaan,

Are you using TRY/CATCH blocks in your code? MSDN - Reference for TRY/CATCH.

They would allow you to capture the error and do whatever you want with it i.e. write it to your custom error logs and then email it out (it's what I prefer and what we've done at places I've worked before).

Regards,

Dave

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rehaan avatar image rehaan commented ·

hi @WRBI

Actually a small change ,

I want to do a check whether packetid exists in transferdata table before i do insert into it so that i avoid primary key violation.

But i am doing insert in batches like 1000, so how can i achieve this ?

0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.