CODEWITHSUNDEEP
sql-server-2008-r2
kudlur
kudlur

Reputation: 1213

Capture primary key constraint violation in SQL server 2008

is there a way to automatically capture (either in SQL server log or Window Event Log) constraint violation due to Inserts into SQL server table with duplicate primary key values?

for Ex: When I try to insert duplicate primary key in SQL Server Management Studio, i get the following error

Msg 2627, Level 14, State 1, Line 2 Violation of PRIMARY KEY constraint 'PK_Customer_A4AE64D87F60ED59'. Cannot insert duplicate key in object 'dbo.Customer'. The statement has been terminated.

But i am unable to find that message in SQL server Logs or Windows Event log.

Thanks

Upvotes: 1

Views: 3556

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40309

That information does not get automatically logged by SQL. There are a number of ways to do this, but you would have to do the setup/configuration yourself, and none of them would be simple to implement.

The "simplest" one is to use SQL Profiler, tracking the Exception event, and filtering on the appropriate error number (or numbers, there might be several similar exceptions to trace). This could be problematic if you're want a permanent always-on recording of this information.

Something might be done with the SQL Agent Alert system. I've never used this, and I seem to recall it requring that alerts be written to the Windows event log, so it might not work--but it still might be worth looking in to.

Most anything else requires modifying your code, to trap and track raised errors. This could be very awkward, and details would depend on the natrure and extent of your code base.

Upvotes: 1

Related Questions