Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
sms1_-_repairing_key_violations [2019/02/26 13:48]
smerritt [SQL 2] 		sms1_-_repairing_key_violations [2019/04/03 15:06] (current)
admin [Task Information]
Line 111: Line 111:
  
 ===== SQL 2 ===== ===== SQL 2 =====
- 
- 
  
 <code sql> <code sql>
 // RETURN Orders that have no line items // RETURN Orders that have no line items
-SELECT "Missing OrderID" AS MissingType, OrderID AS ID, STATUS, ClosedDate+SELECT CAST("Missing OrderID" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate
 FROM "Order Database" FROM "Order Database"
-WHERE OrderID NOT IN (SELECT OrderID FROM "Order Details Database")+WHERE OrderID NOT IN (SELECT OrderID FROM "Order Details Database") AND OrderID > 0
 UNION UNION
 // RETURN Estimates that have no line items // RETURN Estimates that have no line items
-SELECT "Missing EstimateID" AS MissingType, OrderID AS ID, STATUS, ClosedDate+SELECT CAST("Missing EstimateID" as Char(30)) AS MissingType, OrderID AS ID, Status, ClosedDate
 FROM "Estimate Database" FROM "Estimate Database"
-WHERE OrderID NOT IN (SELECT OrderID FROM "Estimate Details 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 ORDER BY MissingType DESC, OrderID DESC
 </code> </code>
 +
   - If any missing line items on recent or open orders or estimates are found, discuss options with the customer. Options include:   - 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.   - 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   - Restart the SSLIP and SMS
   - Run SMS   - Run SMS
 +
 +
 ====== Task Information ====== ====== Task Information ======
-  * Last Updated: 2/15/2015+  * Last Updated: 4/03/2019
   * Created By: Michael Nolan   * 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.   * 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.