How to Rebuild Sage 100 Premium Tables
Summary
How to rebuild SQL tables for Sage 100 Premium.
Disclaimer
Backup Warning
Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.
Data Base Warning
These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.) Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.
Support
Sage Customer Support does not provide assistance for issues related to third party products or enhancements, hardware, report customizations, state or federal tax-related questions, or specific accounting questions. Please contact your Sage business partner, network administrator, or accountant for assistance. Please review this document for additional information on the scope of Sage Customer Support Services.
Cause
This table structure is damaged or incorrect. This technique could be use to solve various hard errors when using the Sage 100 application.
Resolution
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.
- Open SQL Management Studio to access SQL Server databases for Sage 100 Premium
- Double click on Server name in the Object Explorer to the left of the screen in SQL Server
- Double click on Databases to expand database tree to see all databases for Sage 100 Premium
- Double click on the Database that is to be correct (MAS_XXX or MAS_SYSTEM)
- Double click on Tables to expand table tree
- From the Toolbar click on the New Query button
- 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
- Execute or F5 to run the script just created
- From the Toolbar click on the New Query button
- Find the table that is to be replaced (Sales Order History will be used in this example)
- Right click on SO_SalesOrderHistoryDetail > Script Table As > Drop and Create To > New Query Editor Window
- Execute or F5 to run the script just created to delete the table out and replace it with a brand new blank table
- From the Toolbar click on the New Query button
- Enter the following Statement to copy data over to the new table (Sales Order History will be used in this example)
- INSERT SO_SalesOrderHistoryDetail SELECT * FROM HistoryDetailBackup
- Executed or F5 to run the script just created to copy data back to the new table
Support
If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.