Results Database Utility is simple tool that create an entirely new Results database, create the tables for an empty database or upgrade an existing Results database from a previous version. In cases of upgrading, the tool automatically detects the existing database tables and adds or changes tables as needed. This application is included with the CM4D Results Services installation and should only be run by DBA users or someone with a clear knowledge of CM4D and its databases.
The version of CM4D and the version of the Results Database Utility must match. Do not run a version of the Results Database Utility that was not delivered with the version of CM4D Results Services you have installed.
This utility is located in the following directory: .\Applied Tech Systems\ATS CM4D Result Services\Results Database Utility
To ensure you have sufficient system privileges to run the database utilities, it is recommended executing the tool using 'Run as administrator'.
The Results Database Utility requires a connection to the SQL database. By default, this string is obtained from the Cm4d.4ds file (configured with DB Connect) registered with your CM4D installation. If the 4DS file is not reachable, you are prompted to enter the string manually in the command window.
The Results Database connection information can be set in Site Manager before you run Results Database Utility. The Test Connection will fail if this is the first time the Results Database Utility is run (as the database does not yet exist). This is acceptable as long as you are sure that the information entered is correct.
If your Results Database configuration uses Windows Authentication, running the Results Database Utility (as Administrator) does not recognize the 4ds connection string as valid. Since the 4ds connection fails, you are prompted to enter the connection information manually. When entered manually, Windows Authentication is valid. Workarounds include using SQL Authentication for your Results database credentials or simply enter the connection information manually when running the Results Database Utility.
Connection information may also be entered as command line arguments when running the application via Command Prompt.
See the topic Database Utilities for more information on how to Run Utility via Command Prompt Line Arguments.
Some organizations may have a dedicated Database Administrator outside of CM4D users that creates and maintains all databases, or may need to run Results Database Utility separately from CM4D for another reason. In such cases, the information here can be provided to a DBA or other qualified user that will create the CM4D Results Database. The Results Database Utility is included with the CM4D Results Services installation. After installation, the Results Database Utility folder can be copied directly from the CM4D Results Services installation folder and delivered separately to your DBA. The DBA needs to use either a command line argument or the launch settings file to connect to the Results database before running Results Database Utility.
See the topic Results Database for the DBA for more information.
Based on the Database connection information provided, the utility will determine what actions can be applied. There are two sections:
Run Results Database Utility and hit Y to proceed, or any other key to exit without executing any changes to the database.
The Results Database Utility is a console app that creates a new database with the latest version or updates an existing CM4D Results database. The utility can either connect using the CM4D database connection (Cm4d.4ds) file or via manual input of the database connection information.
The next step is dependent on the previous selection:
If you created the Results database object in SSMS before running the Results Database Utility, there are some settings that must be set manually in SQL server. Your SQL Server may already use some of these settings by default, but you should confirm these settings are correct or you may have issues with the Results database.
After running the Results Database Utility tool, open SQL Server to confirm that the database object was created with the correct properties.
By default, the Results database is configured to go by Calendar Year (Jan1-Dec31). However, if your organization uses a fiscal year you can customize your Results database to use a specific Start Month/Day. This allows search parameters which include the year parameter to adhere to the set fiscal year.
To set these dates, open the appsettings.json file from the .\CM4D\Database Utilities\Results Database Utility directory and modify the following information:
If you make these changes after the Results database has been created/updated, simply re-run the Results Database Utility to apply the modified app settings.
See also:
Can we improve this topic?