The Premium version of Sage 100 uses Microsoft SQL for its database. The ability to correct invalid information in the Sage 100 records is much easier and faster using Microsoft SQL Server Management Studio (SSMS), than the Data File Display and Maintenance (DFDM) feature required to correct the Providex database used for the Standard and Advanced versions. The DFDM feature requires users to access each record, one at a time, to edit the data.
Using standard SQL commands, users can update hundreds, thousands, and hundreds of thousands of records in seconds. If only few records need correcting, users can open the table directly and scroll through and change the data in the table; much like changing data in cells of an Excel Spreadsheet. Even more valuable, the commands can be saved and *.sql files and opened and run at any time the fix is needed.
While being extremely more efficient in correcting invalid data, the SSMS is equally efficient in corrupting and destroying data, so use extreme caution and always, always have a valid backup before attempting to correct data. Testing the commands on a copied test company prior to running the command on an actual company is strongly recommended.
Examples of the three most common issues and how to correct the bad data using SSMS are detailed.
Normally this issue occurs when an AR cash receipt batch was deleted after items had been selected on a customer’s account. When users attempt to re-enter the cash receipt for the customer the invoices are not available to select. To correct, open SSMS on the server and choose the "New Query" button in the upper left of corner of the application. Select the database from the drop-down field below the “New Query” button.
The database name will be “MAS_” and your company’s 3-character company code. In the large white area now open on the right side of the application, to reset all records, enter the following:
After the query is run the message tab will return the number of records updated:
To update only one customer, enter the following:
Again, the “Messages” screen will inform of the number of records updated.
Sometimes Vendor Invoices cannot be selected for payment. Typically the issue occurs after an Invoice Selection batch was deleted and the flag on the data record was not reset correctly after the delete. To correct, open SSMS on the server and choose the "New Query" button in the upper left of corner of the application. Select the database from the drop-down field below the “New Query” button.
The database name will be “MAS_” and your company’s 3-character company code. In the large white area now open on the right side of the application, to reset all records, enter the following:
After the query is run the message tab will return the number of records updated:
To update only one vendor enter to following:
Sometimes when check printing is interrupted, users will be prompted with a message that the Bank Code is use when they attempt to print the checks again. To correct, open SSMS on the server and choose the "New Query" button in the upper left of corner of the application. Select the database from the drop-down field below the “New Query” button.
The database name will be “MAS_” and your company’s 3-character company code. In the large white area now open on the right side of the application, to reset all records, enter the following:
To fix only one bank code enter the following:
The three examples are very simple and efficient resolutions to common problems. Too often in the past the time to correct numerous records often caused a decision to restore backups; losing all work completed since the backup was completed. This is no longer a issues for the Sage 100 Premium users. Another significant feature of using the SSMS is the ability to save the commands *.sql query files that may be run at any time. Users do not need to be SQL savvy to run the files. They only need to open the files in the SSMS and select the "Execute" button to run the saved commands.