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

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.

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.

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

• 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

.

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

// RETURN Orders that have no line items
SELECT CAST("Missing OrderID" AS CHAR(30)) AS MissingType, OrderID AS ID, STATUS, ClosedDate
FROM "Order Database"
WHERE OrderID NOT IN (SELECT OrderID FROM "Order Details Database") AND OrderID > 0
UNION
// RETURN Estimates that have no line items
SELECT CAST("Missing EstimateID" AS CHAR(30)) AS MissingType, OrderID AS ID, STATUS, ClosedDate
FROM "Estimate Database"
WHERE OrderID NOT IN (SELECT OrderID FROM "Estimate Details Database") AND OrderID > 0
UNION
SELECT CAST("Missing Order ProductCode" AS CHAR(30)) AS MissingType, OrderID AS ID, STATUS, ClosedDate
FROM "Order Database"
WHERE OrderID IN (SELECT OrderID FROM "Order Details Database" WHERE ProductCode = '(none)') AND OrderID > 0
UNION
SELECT CAST("Missing Estimate ProductCode" AS CHAR(30)) AS MissingType, OrderID AS ID, STATUS, ClosedDate
FROM "Estimate Database"
WHERE OrderID IN (SELECT OrderID FROM "Estimate Details Database" WHERE ProductCode = '(none)') AND OrderID > 0
ORDER BY MissingType DESC, OrderID DESC

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

• Last Updated: 4/03/2019
• Created By: Michael Nolan
• 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.