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:44]
smerritt [SQL 1] 		sms1_-_repairing_key_violations [2019/04/03 15:06] (current)
admin [Task Information]
Line 112: Line 112:
 ===== SQL 2 ===== ===== 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 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.