Error Message

Various forms that include the words “Key Violation” in them.

Explanation of Error

Every row in the database has a unique “Key”. This is the ID that the database uses to identify that row. No two rows in the same table can have the same Key (since it would mean the database could not tell which one you wanted).

A key violation occurs when SMS tries to write a record with a Key that is already being used in the system. This most commonly occurs in two instances:

  • when a database or index corruption has occurred, so different clients are getting different results when pulling Key values, and
  • when two users are both saving data, and the network latency (delay) is such that they don't realize the other is about to use the same Key.

In SMS 8.9, the responsibility for tracking and obtaining Keys for Orders and Estimates was moved from SMS to the SSLIP to avoid any potential conflicts. It can still occur, but this error is much, much less common in those versions.

Note: In many cases (perhaps 50%) there is no underlying database corruption and simply closing all copies of SMS and the SSLIP and restarting it is sufficient to reset the BDE and resume normally. However, there is no way to know which case applies, so the steps below should be followed each time.

Severity

This is a high-severity error. The Key Violation in itself means the DBE on the client system is out of sync with the database. Hence, any future saving of data on that or other systems may be compromised.

Tier 1 Actions

  1. Close all copies of SMS and the SSLIP and make a backup of the data folder.
  2. Run Computer Management (Start | Run … CompMgmt.msc) to confirm that no files in the ..\Cyrious$\SMS hierarchy are open. If they are, close them. Refresh the screen after closing files to confirm no additional files appear.
  3. Confirm you have the correct Data folder by looking at the modified date of the files in the data folder.
  4. Run the dbrebuild utility in ..\\Cyrious$\SMS\Utilities\dbrebuild
  5. Click on the Verify All Tables button. This will scan all the tables in the database for errors.
    • Note any tables with errors. This will need to be researched afterwards.
    • If all tables say “Could Not Verify”, reboot the server, close the SSLIP, and restart the process.
  6. If any tables are found with errors, check those tables and click Rebuilt Checked.
  7. If any tables could not be rebuilt, elevate to Tier 2 support.
  8. Close dbrebuild
  9. Open the IDRepair utility
  10. Scan the tables
  11. Click the Execute button if any tables have a check in the checkbox
  12. Open the database browser utility, DBBrowser.
  13. Check for any blank records or negative IDs
  14. Delete blank records
  15. Try removing the '-' from small negative IDs, if you get a key violation delete it
  16. Delete large negative IDs
  17. Run SQL 1 to locate conflicting ID Numbers

1. Run this query for the following tables and associated IDs as appropriate:

  • Adjustment Database - AdjustmentID
  • Contact Database - ContactID
  • Customer Database - CustomerID
  • Estimate Database - OrderID
  • Order Database - OrderID
  • Order Details Database - OrderDetailID
  • Estimate Details Database - OrderDetailID
  • Estimate Modifiers Database - ID
  • Order Modifiers Database - ID
  • Deleted Records Database - ID (SMS > 8.9)
  • GL Database - ID
  • Payments Database - PaymentID
  • Product Database - ProductID

2. Run **SQL 2** to located conflicting ID numbers

.

SELECT ContactID, COUNT (ContactID) AS CustCNT
FROM "Contact Database"
GROUP BY ContactID
ORDER BY CustCNT DESC
  • Note that any word can be substituted for “Contact” above in each area where the word is found in the query, depending on the database in which you're running the search
  • Example :
SELECT  OrderID, COUNT(OrderID) AS CustCNT
FROM "Order Database"
GROUP BY OrderID
HAVING COUNT(OrderID) > 1
ORDER BY CustCNT DESC

code_formatsql|code format="SQL"

Select deleteditemid, tablename, count(*) as Cnt From “Deleted Records Database”

Group By deleteditemid, tablename

Having Count(*) > 1

<code>

  • If duplicate IDs are found, Cyrious Technicians Elevate to Tier 2 Support.
SQL 3
  1. Run the following queries to search for missing line items on orders or estimates.

code_formatsql|code format="SQL"

Return Orders and Estimates that have no line items select “Missing OrderID ” as MissingType, OrderID as ID, Status, ClosedDate from “Order Database” where OrderID not in (Select OrderID from “Order Details Database”) union Return Estimates that have no line items

select “Missing EstimateID” as MissingType, OrderID as ID, Status, ClosedDate

from “Estimate Database”

where OrderID not in (Select OrderID from “Estimate Details Database”)

order by MissingType desc, OrderID desc

;

<code>

  1. If any missing line items on recent or open orders or estimates are found, discuss options with the customer. Options include:
    • If the order is closed, the customer can do nothing and the only loss should be the historical information about those line items.
    • The customer can just edit the orders (if open) and re-enter the line items. Especially when payments are on the order, care must be taken to put the information in for the correct total before saving.
    • If a backup that contains those items can be found, Cyrious Technicians can re-insert the missing data from the backup. Elevate to Tier 2 Support. See sms2_-_inserting_missing_records_from_a_backup_table for those steps.
  2. If errors in any tables were found during the ReBuild process, manually browse those tables in the dbbrowser|DBBrowser.
    • Check that the indexes are still present (bottom left of the first tab).
    • Move to the bottom of the table. Tab across the table. Look for any errors or data corruptions.
    • If any exist, open blob fields from the last 5 records (by double-clicking on the cell) and check for data corruption.
    • If any problems are found, Elevate to Tier 2 Support.
  3. Restart the SSLIP and SMS
  4. Run SMS

Task Information

  • Last Updated: 2/15/2015
  • Created By: Michael Nolan
  • Verified By:
  • Applies in all versions. More common in versions SMS 8.6 and below due to a change in how Keys are handled for orders and estimates in SMS 8.9.
You could leave a comment if you were logged in.