Solution Properties
Solution ID:
224924750069387
Last Modified Date:
Mon May 02 23:35:49 UTC 2022
Taxonomy Path:
Category//Data
Author:
[email protected]
How To Rebuild Sage 100 Premium Tables
Description
Cause

The table structure is damaged or incorrect.  This technique could be use to solve various hard errors when using the Sage 100 application.

Resolution
[BCB:1:Backup warning:ECB]
[BCB:2:Database warning:ECB]
[BCB:5:Third-party support:ECB]

Back up the MAS_XXX database, back up the table, then drop (delete)the table, recreate the table, insert the records back in. When recreating the table should use a demo company or standard installation. If using another installation should be same version. Prepare the script first so you know you have it handy. This example is using SO_SalesOrderHistoryDetail table, any table can be used as needed.  These are standard SQL commands, the SQL DBA could use other methods.

  1. Open SQL Management Studio to access SQL Server databases for Sage 100 Premium
  2. Double click on Server name in the Object Explorer to the left of the screen in SQL Server
  3. Double click on Databases to expand database tree to see all databases for Sage 100 Premium
  4. Double click on the Database that is to be corrected (MAS_XXX or MAS_SYSTEM)
  5. Double click on Tables to expand table tree
  6. From the Toolbar click on the New Query button
  7. Enter the following Statement to copy data over to new temporary table (Sales Order History will be used in this example)
    • SELECT * INTO HistoryDetailBackup FROM SO_SalesOrderHistoryDetail
  8. Execute or F5 to run the script just created 
  9. From the Toolbar click on the New Query button
  10. Find the table that is to be replaced (Sales Order History will be used in this example)
  11. Right click on SO_SalesOrderHistoryDetail > Script Table As > Drop and Create To > New Query Editor Window 
  12. Execute or F5 to run the script just created to delete table out and replace with a brand new blank table
  13. From the Toolbar click on the New Query button
  14. Enter the following Statement to copy data over to new table (Sales Order History will be used in this example)
    • INSERT SO_SalesOrderHistoryDetail SELECT * FROM HistoryDetailBackup
  15. Execute or F5 to run the script just created to copy data back to the new table

DocLink: How to Rebuild Key Files in Sage 100
Attachment: 245_20230425124001_sql.txt
Defect ID
Steps to duplicate
Related Articles