Differences

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

--- sms1_-_repairing_key_violations [2019/02/26 13:46]
smerritt [SQL 2]
+++ sms1_-_repairing_key_violations [2019/04/03 15:06] (current)
admin [Task Information]
@@ Line 111: / Line 111: @@
 ===== 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"
-Select deleteditemid, tablename, count(*) as Cnt From "Deleted Records 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
-Group By deleteditemid, tablename
+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
-Having Count(*) > 1
+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>
-    * 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 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.