Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
sms_error_-_key_violation [2019/01/27 11:30]
127.0.0.1 external edit 		sms_error_-_key_violation [2019/07/18 12:53] (current)
orichards [Open Database Browser]
Line 1: Line 1:
-======  ====== +====== Key Violation ======
- +
  
 Each record in the SMS datbase (and most others) has a //key// field. This field uniquely identifies the record (row of data) and is used by the database to quickly find the record. In SMS tables, the first column in the table (usually ending in "ID") is used as the key field. Each record in the SMS datbase (and most others) has a //key// field. This field uniquely identifies the record (row of data) and is used by the database to quickly find the record. In SMS tables, the first column in the table (usually ending in "ID") is used as the key field.
- 
- 
  
 A "Key Violation" is the computerese phrase saying that you are trying to save a record with the same "key" as one already in the database. Since this field must be unique, this is not allowed. A "Key Violation" is the computerese phrase saying that you are trying to save a record with the same "key" as one already in the database. Since this field must be unique, this is not allowed.
- 
- 
  
 ===== Steps ===== ===== Steps =====
  
 **Make sure all users are out of SMS while the following is being done.** **Make sure all users are out of SMS while the following is being done.**
 +==== Open Database Browser ====
  
- +  - Make a backup of the ~\Cyrious\SMS\Data folder by copying the folder and then renaming it with today's date. 
- +  - Run the [[:dbrebuild|DBRebuild]]. 
-==== Open Database Browser ==== +  - Run the [[:id_repair|IDRepair]] utility (unless customer has a flag stating otherwise)
-  - Make a backup of the ~\Cyrious\SMS\Data folder. +
-  - Run the [[dbrebuild]]. +
-  - Run the [[id_repair]] utility (unless customer has a flag stating otherwise)+
   - Open the DBBrowser.   - Open the DBBrowser.
  
  
 +==== Look for Bad Data At the Top of Each Table ====
  
-==== Look for Bad Data At the Top of Each Table ==== 
   - Delete any blank records at the top of the database (no entries in ANY field in a given row).   - Delete any blank records at the top of the database (no entries in ANY field in a given row).
   - Records with blank key fields. If the data looks meaningful, consult technical support before deleting this record.   - Records with blank key fields. If the data looks meaningful, consult technical support before deleting this record.
-    * If a blank ID records turns up with valid information, then an unused ID number should be assigned to the record.+      * If a blank ID records turns up with valid information, then an unused ID number should be assigned to the record.
   - Delete any large negative values records at the top of the database (i.e. -117346859).   - Delete any large negative values records at the top of the database (i.e. -117346859).
-  - Renumber any normal size negative number with the psotive value (i.e. -3456 rekey as 3456). +  - Renumber any normal size negative number with the positive value (i.e. -3456 rekey as 3456). 
-    * If you get a key violation renumbering it (meaning the positive record already exists), delete the negative record. +      * If you get a key violation renumbering it (meaning the positive record already exists), delete the negative record.
- +
  
 ==== Query the Tables for Problems ==== ==== Query the Tables for Problems ====
  
 +<html><div style="margin-left: 20px;"> **Before running queries**  </div></html>
  
 +<html><div style="margin-left: 40px;"> **Determining what tables need to be checked will save time**  </div></html>
  
-<html><div style="margin-left: 20px;"> **Before running queries** </div></html>+<html><div style="margin-left: 40px;">> (i.e. Customer reports the error when editing an estimate, check the Estimate, Estimate Details, and Estimate Modifiers table; Customer reports the error when posting a payment, check the Adjustment, GL, and Payment tables; when editing an order, check the Adjustment, GL, Order, Order Details, and Order Modifiers tables; if not sure run all the major tables) </div></html>
  
- 
- 
-<html><div style="margin-left: 40px;"> **Determining what tables need to be checked will save time** </div></html> 
- 
- 
- 
-<html><div style="margin-left: 40px;">> (i.e. Customer reports the error when editing an estimate, check the Estimate, Estimate Details, and Estimate Modifiers table; Customer reports the error when posting a payment, check the Adjustment, GL, and Payment tables; when editing an order, check the Adjustment, GL, Order, Order Details, and Order Modifiers tables; if not sure run all the major tables) </div></html> 
   - Run these all queries for the following tables and associated IDs as appropriate:   - Run these all queries for the following tables and associated IDs as appropriate:
-    * Adjustment Database - AdjustmentID +      * Adjustment Database - AdjustmentID 
-    * Contact Database - ContactID +      * Contact Database - ContactID 
-    * Customer Database - CustomerID +      * Customer Database - CustomerID 
-    * Estimate Database - OrderID +      * Estimate Database - OrderID 
-    * Order Database - OrderID +      * Order Database - OrderID 
-    * Order Details Database - OrderDetailID +      * Order Details Database - OrderDetailID 
-    * Estimate Details Database - OrderDetailID +      * Estimate Details Database - OrderDetailID 
-    * Estimate Modifiers Database - ID +      * Estimate Modifiers Database - ID 
-    * Order Modifiers Database - ID +      * Order Modifiers Database - ID 
-    * Deleted Records Database - ID (SMS > 8.9) +      * Deleted Records Database - ID (SMS > 8.9) 
-    * GL Database - ID +      * GL Database - ID 
-    * Payments Database - PaymentID +      * Payments Database - PaymentID 
-    * Product Database - ProductID +      * Product Database - ProductID 
-    * Deleted Records (only in 8.9) +      * Deleted Records (only in 8.9) 
-  - Run **SQL 1** to locate conflicting ID Numbers. +  - Run **SQL 1**  to locate conflicting ID Numbers. 
-    - Each record in a database must have a unique and distinct primary key ID number (i.e. Contact Database uses "ContactID" as a primary key in this database and the ContactID field is a foreign key in other tables which are linked to it). +      - Each record in a database must have a unique and distinct primary key ID number (i.e. Contact Database uses "ContactID" as a primary key in this database and the ContactID field is a foreign key in other tables which are linked to it). 
-    - If an entry is posted, such as updating a contact record using ContactID # 5642 and one already exists for a different contact, then this will result in an immediate key violation. The ID number must be changed to one that is unused. +      - If an entry is posted, such as updating a contact record using ContactID # 5642 and one already exists for a different contact, then this will result in an immediate key violation. The ID number must be changed to one that is unused. 
-    - If the information is the same in both records, you will need to delete one of the 2 records. +      - If the information is the same in both records, you will need to delete one of the 2 records. 
-    - In some cases, the primary key is made up of two fields like the GL (i.e. the ID and the StoreID make up the primary key.) +      - In some cases, the primary key is made up of two fields like the GL (i.e. the ID and the StoreID make up the primary key.) 
-    - If you have an ID that is the same, but the information is different in this case, you will need to assign a new ID +      - If you have an ID that is the same, but the information is different in this case, you will need to assign a new ID 
-    - If you have duplicate ContactIDs in the Contact table, use **SQL 2** to correct all the appropriate tables: +      - If you have duplicate ContactIDs in the Contact table, use **SQL 2**  to correct all the appropriate tables: 
-    - **SQL 3** (far below) can be used to fix duplicate keys in other tables. +      - **SQL 3**  (far below) can be used to fix duplicate keys in other tables.
- +
- +
 ===== SQL 1 ===== ===== SQL 1 =====
- 
- 
  
 <code sql> <code sql>
-SELECT ContactID, Count (ContactID) as CustCNT+SELECT ContactID, COUNT (ContactID) AS CustCNT
 FROM "Contact Database" FROM "Contact Database"
 GROUP BY ContactID GROUP BY ContactID
 ORDER BY CustCNT DESC ORDER BY CustCNT DESC
 </code> </code>
-    * 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 : 
- 
  
 +  * 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 :
  
 <code sql> <code sql>
-SELECT  OrderID, Count(OrderID) as CustCNT+SELECT  OrderID, COUNT(OrderID) AS CustCNT
 FROM "Order Database" FROM "Order Database"
 GROUP BY OrderID GROUP BY OrderID
-HAVING Count(OrderID) > 1 +HAVING COUNT(OrderID) > 1 
-ORDER BY CustCNT Desc+ORDER BY CustCNT DESC
 </code> </code>
- 
- 
  
 ===== SQL 2 ===== ===== SQL 2 =====
- 
- 
  
 <code sql> <code sql>
-// Get a list of all duplicated contacts +// GET a list OF ALL duplicated contacts 
-Select ContactID +SELECT ContactID 
-From "Contact Database" +FROM "Contact Database" 
-Group by ContactID +GROUP BY ContactID 
-HAVING cOUNT(cONTACTid) > 1+HAVING COUNT(cONTACTid) > 1
 ; DupContactList ; DupContactList
-Create Index TempContactIndex1 on DupContactList (ContactID)+CREATE INDEX TempContactIndex1 ON DupContactList (ContactID)
 ; ;
-// Get the Contact, Customer pairing +// GET the Contact, Customer pairing 
-select ContactID as OldContactID, CustomerID, ContactID as NewContactID +SELECT ContactID AS OldContactID, CustomerID, ContactID AS NewContactID 
-from "Contact Database" +FROM "Contact Database" 
-where ContactID in (Select ContactID from DupContactList)+WHERE ContactID IN (SELECT ContactID FROM DupContactList)
 ; DupContacts ; DupContacts
-// Make sure you reset the NewContactID to be greater than the exsting IDs +// Make sure you reset the NewContactID TO be greater than the exsting IDs 
-// Update the 22000 to the last ID in the database +// UPDATE the 22000 TO the LAST ID IN the DATABASE 
-update DupContacts D1 +UPDATE DupContacts D1 
-set NewContactID = OldContactID  + 22000 +SET NewContactID = OldContactID  + 22000 
-where CustomerID in (Select Max(CustomerID) from DupContacts as D2 where D2.OldContactID = D1.OldContactID )+WHERE CustomerID IN (SELECT MAX(CustomerID) FROM DupContacts AS D2 WHERE D2.OldContactID = D1.OldContactID )
 ; ;
-Create Index TempContactIndex2 on DupContacts (OldContactID)+CREATE INDEX TempContactIndex2 ON DupContacts (OldContactID)
 ; ;
-// Update the Contact Database +// UPDATE the Contact DATABASE 
-update "Contact Database" C +UPDATE "Contact Database" C 
-set ContactID = (select NewContactID from DupContacts D where D.OldContactID = C.ContactID and D.CustomerID = C.CustomerID) +SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.ContactID AND D.CustomerID = C.CustomerID) 
-where ContactID in (select ContactID from DupContactList)+WHERE ContactID IN (SELECT ContactID FROM DupContactList)
 ; ;
-// Update the Order Database +// UPDATE the ORDER DATABASE 
-update "Order Database" O +UPDATE "Order Database" O 
-set ContactID = (select NewContactID from DupContacts D where D.OldContactID = O.ContactID and D.CustomerID = O.CustomerID) +SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = O.ContactID AND D.CustomerID = O.CustomerID) 
-where ContactID in (select ContactID from DupContactList)+WHERE ContactID IN (SELECT ContactID FROM DupContactList)
 ; ;
-// Update the Order Database +// UPDATE the ORDER DATABASE 
-update "Estimate Database" O +UPDATE "Estimate Database" O 
-set ContactID = (select NewContactID from DupContacts D where D.OldContactID = O.ContactID and D.CustomerID = O.CustomerID) +SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = O.ContactID AND D.CustomerID = O.CustomerID) 
-where ContactID in (select ContactID from DupContactList)+WHERE ContactID IN (SELECT ContactID FROM DupContactList)
 ; ;
-// Update the Customers Primary Contact ID +// UPDATE the Customers PRIMARY Contact ID 
-update "Customer Database" C +UPDATE "Customer Database" C 
-set PrimaryContactID = (select NewContactID from DupContacts D where D.OldContactID = C.PrimaryContactID and D.CustomerID = C.CustomerID) +SET PrimaryContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.PrimaryContactID AND D.CustomerID = C.CustomerID) 
-where PrimaryContactID in (select ContactID from DupContactList)+WHERE PrimaryContactID IN (SELECT ContactID FROM DupContactList)
 ; ;
-// Update the Customers AP Contact ID +// UPDATE the Customers AP Contact ID 
-update "Customer Database" C +UPDATE "Customer Database" C 
-set APContactID = (select NewContactID from DupContacts D where D.OldContactID = C.APContactID and D.CustomerID = C.CustomerID) +SET APContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.APContactID AND D.CustomerID = C.CustomerID) 
-where APContactID in (select ContactID from DupContactList)+WHERE APContactID IN (SELECT ContactID FROM DupContactList)
 ; ;
 </code> </code>
-    - SQL 3 can be used to fix duplicate keys in the Adjustment, GL, or Payments tables. 
- 
  
 +  - SQL 3 can be used to fix duplicate keys in the Adjustment, GL, or Payments tables.
  
 ===== SQL 3 ===== ===== SQL 3 =====
- 
- 
  
 <code sql> <code sql>
-Select ID, Count (ID) as CNT +SELECT ID, COUNT (ID) AS CNT 
-From "GL Database" +FROM "GL Database" 
-Group by ID +GROUP BY ID 
-Order by CNT Desc+ORDER BY CNT DESC
 ;tempa ;tempa
-Select ID +SELECT ID 
-From "tempa" +FROM "tempa" 
-where CNT > 1+WHERE CNT > 1
 ;tempb ;tempb
-Update "GL Database" +UPDATE "GL Database" 
-Set ID = ID + 1XXXXXX +SET ID = ID + 1XXXXXX 
-where ID in (Select ID From Tempb) and StoreID = 1+WHERE ID IN (SELECT ID FROM Tempb) AND StoreID = 1
 </code> </code>
  
-**note:** XXXXXX should represent the number places of the last entry in the table, so ID goes from XXXXXX (6 characters) to 1XXXXXX (1+6 characters). You must then manually resequence the new IDs +**note:**  XXXXXX should represent the number places of the last entry in the table, so ID goes from XXXXXX (6 characters) to 1XXXXXX (1+6 characters). You must then manually resequence the new IDs
-    - SQL 4 +
  
 +  - SQL 4
  
 This is only used for a large quantity of duplicate IDs in the GL and Payments tables. It can NOT be used on any other table due to potential key issues. This is only used for a large quantity of duplicate IDs in the GL and Payments tables. It can NOT be used on any other table due to potential key issues.
- 
- 
  
 Only use this SQL if you have more than 50 duplicate IDs Only use this SQL if you have more than 50 duplicate IDs
- 
- 
  
 ===== SQL 4 ===== ===== SQL 4 =====
- 
- 
  
 <code sql> <code sql>
-Select ID as OldID, +SELECT ID AS OldID, 
-       Count (ID) as CNT, +       COUNT (ID) AS CNT, 
-       Min(StoreID) as FirstStoreID, +       MIN(StoreID) AS FirstStoreID, 
-       Max(StoreID) as LastStoreID, +       MAX(StoreID) AS LastStoreID, 
-       cast(0 as Integer) NewID +       CAST(0 AS INTEGER) NewID 
-From "GL Database" +FROM "GL Database" 
-Group by ID +GROUP BY ID 
-having Count(ID) > 1 +HAVING COUNT(ID) > 1 
-Order by CNT Desc+ORDER BY CNT DESC
 ;TempA2 ;TempA2
-create index TempNewIDIndexB on TempA2 (OldID, LastStoreID)+CREATE INDEX TempNewIDIndexB ON TempA2 (OldID, LastStoreID)
 ; ;
-update TempA2 T1 +UPDATE TempA2 T1 
-set NewID = 1 +SET NewID = 1 
-                   + (select Max(ID) from "GL Database") +                   + (SELECT MAX(ID) FROM "GL Database") 
-                   + (select Count(*) from TempA2 T2 where T2.OldID < T1.OldID)+                   + (SELECT COUNT(*) FROM TempA2 T2 WHERE T2.OldID < T1.OldID)
 ; ;
-update "GL Database" GL +UPDATE "GL Database" GL 
-set ID = (select NewID from TempA2 T where GL.ID = T.OldID), StoreID = 1 +SET ID = (SELECT NewID FROM TempA2 T WHERE GL.ID = T.OldID), StoreID = 1 
-where StoreID = (select LastStoreID from TempA2 T where GL.ID = T.OldID)+WHERE StoreID = (SELECT LastStoreID FROM TempA2 T WHERE GL.ID = T.OldID)
 ; ;
-select * from TempA2+SELECT * FROM TempA2
 </code> </code>
  
-**Note:** You will need to adjust the Table name and ID name +**Note:**  You will need to adjust the Table name and ID name
- +
- +
- +
-See Also +