Differences

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

--- sms1_-_repairing_key_violations [2019/01/27 11:31]
127.0.0.1 external edit
+++ sms1_-_repairing_key_violations [2019/04/03 15:06] (current)
admin [Task Information]
@@ Line 90: / Line 90: @@
 ===== SQL 1 =====
 <code sql>
-SELECT ContactID, Count (ContactID) as CustCNT
+SELECT ContactID, COUNT (ContactID) AS CustCNT
 FROM "Contact Database"
 GROUP BY ContactID
 ORDER BY CustCNT DESC
 </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>
-SELECT  OrderID, Count(OrderID) as CustCNT
+SELECT  OrderID, COUNT(OrderID) AS CustCNT
 FROM "Order Database"
 GROUP BY OrderID
-HAVING Count(OrderID) > 1
+HAVING COUNT(OrderID) > 1
-ORDER BY CustCNT Desc
+ORDER BY CustCNT DESC
 </code>
 ===== SQL 2 =====
+<code sql>
+// 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
+</code>
-[[code_formatsql|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==
-    - Run the following queries to search for missing line items on orders or estimates.
-[[code_formatsql|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>
   - 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.
+      * 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.
+      * 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.
+      * 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.
   - 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).
+      * 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.
+      * 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 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__.
+      * If any problems are found, __Elevate to Tier 2 Support__.
   - Restart the SSLIP and SMS
   - Run SMS
 ====== Task Information ======
-  * Last Updated: 2/15/2015
+  * Last Updated: 4/03/2019
   * 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.