This utility is used to compare the current company data dictionaries to the master data dictionary to ensure all fields, indexes, and tables are current for the company. In addition, this utility can be used to add new index definitions to an existing company when they are missing.
Important Note: The master company dictionary that was shipped with AccuBuild Version 8.3x was missing index definitions for the following tables: PMEvents, PMFormXR, PMTasks, and SalesAssmbly. When an attempt is made to open a missing index, Exception Error 5016 is displayed: 'Advantage could not find an index order with the specified name'. This utility was created to address this issue.
•Master Company Only
This checkbox is enabled by default in order to ignore comparisons on the customer's databases that involve Tailored Solutions or 3rd Party Integrations so that these types of changes are no longer reported as differences. When this box is checked, the system only verifies that all of the standard dictionary items exist in the customer's databases, and the full TWO WAY comparison between each dictionary is disabled. If the new checkbox is unchecked when the comparison is executed, a TWO WAY Comparison will be made.
•Show Detail Results
| This is used to show details of the dictionary comparisons. When unchecked, only the errors and warnings are displayed. |
•Show Auto Create Settings
| This option is used to include the current auto create settings of all tables. When the auto create setting of a table is enabled, the table and associated index and memo tables will be created automatically when the table is opened if they do not already exist. |
| Use this button to print the text in the Result Box. |
•Compare Dictionaries
| Use this button to compare the company, user, or global dictionary definitions to the master definitions and report any differences. All tables, indexes, and views will be compared. |
•Update Indexes
| Use this button to force the current company indexes to match the master company dictionary definitions. If you are receiving a 5016 error as described above, then the error can be corrected by running this option and then running the Index Company Tables option from the File Menu to make sure all company indexes are created successfully. |
•Pop Up Menu
| Right-click on the screen to view the pop up menu options |
•Clear Auto Create on Target Company Tables
| When this menu option is checked, all of the AUTO CREATE settings on the company tables will be set to FALSE when the Compare Dictionaries button is pressed. The AUTO CREATE settings are set to FALSE by default each time the company is opened, but these settings can also be changed outside the AccuBuild Program via the ARC Utility. |
•Clear Auto Create on Target User Tables
| When this menu option is checked, all of the AUTO CREATE settings on the user tables will be set to FALSE when the Compare Dictionaries button is pressed. |
•ENHANCEMENT - New features and processes for the SQL Dictionary Comparison Utility [PMID Ref No 14815] Version 10.0.0.5
oThe SQL Dictionary Comparison Utility has been updated with additional comparison routines for the database fields and indexes in order to discover additional differences between the live database tables and the database definitions.
Compare Dictionaries Button: Two new sections will now be included in the Results Window for providing more details for any differences during the database comparisons:
▪FIELDS: The new FIELDS section will identify specific field attribute differences which include the following list (Note: The Red Fields are NOT tested):
❑Field_Type
❑Field_Length
❑Field_Decimal - Not Tested
❑Field_Min_Value
❑Field_Max_Value
❑Field_Can_Be_Null - Not Tested
❑Field_Default_Value
❑Field_Options
Although in most cases, there should be no differences discovered, these additional comparisons are necessary to identify any special settings for specific companies that may be a result of a tailored solution. The most important attribute in the above list is the Field_Type which SHOULD always match the database definitions. For example, an integer field type and an autoinc field type are both integer type fields, however, the autoinc field will NEVER allow a duplicate value which is a very important distinction that the AccuBuild Program depends on.
When the database fields are compared, three temporary tables are created in the user folder which contain the field settings for the live company and the database definitions, along with a third table for the database differences:
❑__ABDictionaryFieldInfo.ADT - Database Definitions
❑__ABCompanyFieldInfo.ADT - Live Company Definitions
❑__ABFieldErrors.ADT - Database Field Differences
▪INDEXES: The new INDEXES section will identify specific index attribute differences which include the following list:
❑Index_File_Name
❑Index_Expression
❑Index_Options
❑Index_Key_Length
There should be no differences discovered for the index files with the Index_Expression and the Index_Options being the most important attributes. For example, the employee list file (PERSONAL) contains an index on the employee number field (E_NO) which is contained in the Index_Expression. However, the Index_Options contains a UNIQUE Setting that prevents duplicate employee numbers from being added to the employee list.
When the database indexes are compared, three temporary tables are created in the user folder which contain the index settings for the live company and the database definitions, along with a third table for the database differences:
❑__ABDictionaryIndexInfo.ADT - Database Definitions
❑__ABCompanyIndexInfo.ADT - Live Company Definitions
❑__ABIndexErrors.ADT - Database Field Differences
Update Indexes Button: The process for Updating Missing Index files has been updated to support field functions in the index expressions. For example, with some indexes that use a field with alphanumeric data, such as a Vendor Invoice Number, the UPPER expression is used on the index which eliminates any duplicate invoice issues by comparing the invoice numbers without concern for upper or lower case letters.
In previous versions of AccuBuild, these types of indexes were causing errors and the indexes could not be rebuilt using this button. These types of errors are now corrected in this version. In addition, new Update Indexes Buttons have been added for the User Database and the Global Database Sections:
Technical Notes for AccuBuild Support Technicians: This database comparison utility should be run when you first open the AccuBuild Program so that you can avoid issues with tables that are already opened in your AccuBuild Session. If tables are in use during the Update Indexes process, you will encounter a 7008 error when trying to create missing indexes as shown below. This will usually be due to the table being opened by AccuBuild Program and the required index cannot be created. You will want to run the Execute Procedure from ARC when there are no other users in AccuBuild. Copy the EXECUTE PROCEDURE Code from the Results Text Box by highlighting the text and then pressing CTRL-C to copy the text and paste it into a notepad file which you can then access once ARC is opened: Even if the error code is generated, you will have all of the code you need to create the proper index(s) for the table. One you have successfully created the tables in ARC, you can open AccuBuild and re-run the comparison utility to make sure the index errors are corrected. See the following sample of the 7008 error code:
EXECUTE PROCEDURE sp_CreateIndex90( 'BATCHLST','BATCHLST.adi','PRIMARY','BatchType;Source','',2051,512,'' );
poQuery: Error 7200: AQE Error: State = HY000; NativeError = 7008; [iAnywhere Solutions][Advantage SQL][ASA] Error 7008: The specified table, memo file, or index file was unable to be opened.
•DOCUMENTATION UPDATE - File Structure Help Update on Company Database Dictionaries Version 10.2.0.0
With the release of AccuBuild Version 10.2, new index files have been introduced for the DMFLDIST document management company table which are used by the Invoice Approval Process in Document Management. When you are using the SQL Dictionary Comparison Utility (File Menu), you may encounter a message indicating that 5 of the 6 index files are missing for the DMFLDIST Table as shown:
DMFLDIST-INDEXES:
Target Count: 1 Master Count: 6
1. BYBATCHNO Missing in TARGET DB.
3. BYINVREFNO Missing in TARGET DB.
4. BYNANO Missing in TARGET DB.
5. BYVENDORINVOICENO Missing in TARGET DB.
6. BYVINVNO Missing in TARGET DB.
In order to create these indexes for your company, you can open the Document Routing Screen from the Documents Menu and you will be prompted to create these index files:
Once these index files have been created, you can run the SQL Dictionary Comparison Utility to compare the Company Dictionary files, and the missing index messages for the DMFLDIST table should be corrected.