Database Validation Scripts

The ValidateUDTablePriorToUpgrade script identifies UD_Tables that contain rows in the ObjectMetaData table. If a UD_Table has rows in the ObjectMetaData table, they are not properly converted to V3 panel sources. This can occur if a row has been manually added to the ObjectMetaData table so that the UD_Table can be used as a panel source.

Run this script prior to upgrading to locate all UD_Table entries in the ObjectMetaData table. If rows are identified, this script includes the generated SQL to drop the row from ObjectMetaData. 

1
2
3
4
5
SELECT udt.[TABLE_NAME] AS TableName, 'UD_Table entry has ObjectMetaData entry too' AS Issue,
    'DELETE FROM [dbo].[ObjectMetaData] WHERE [ObjectName] = ''' + omd.[ObjectName] + ''';' AS CorrectiveSQL
  FROM [dbo].[UD_Table] udt
       LEFT OUTER JOIN [dbo].[ObjectMetaData] omd ON udt.[TABLE_NAME] = omd.[ObjectName]
 WHERE omd.[ObjectName] IS NOT NULL;

The data types of UD Tables (Customizer) must match the data that is being stored. Review the UD/Customizer tables to confirm the data types match the associated data being stored. If the data type does not match the stored data, consider updating the data type in Customizer prior to upgrading.

Important! Float is no longer an accepted data type in iMIS EMS. Float data types must be updated to an appropriately sized Numeric. Be aware that changing data types may cause errors on the UD Table Migration portion of the upgrade; for example, if a data type is being updated from Float to Numeric, an arithmetic overflow error can occur.

The below script will detect any UD_Table that contains invalid values in a Float column, which will prevent the table from upgrading/converting to iMIS EMS. Run the script prior to upgrading to iMIS EMS from iMIS 2017. The script returns the table name, column name, and the ID associated with the bad data. If the script returns any rows, the data must be corrected.

DECLARE @sql varchar(max) = '';
SELECT @sql += CASE WHEN LEN(@sql) = 0 THEN '' ELSE 'UNION ALL' END + '
SELECT ''' + TABLE_NAME + ''' AS UDTableWithIssue, ''' + COLUMN_NAME + ''' ColumnWithIncompatableFloat, ID FROM ' + TABLE_NAME + ' WHERE TRY_CAST(' + COLUMN_NAME + ' AS numeric(18,4)) IS NULL
'
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME IN (
    SELECT TABLE_NAME FROM UD_TABLE
    )
   AND DATA_TYPE = 'float'
EXEC(@sql);

The ValidateDocumentSystem2017GA.sql script validates the Document System and hierarchy for an iMIS database. This script checks for orphans and duplicates, and several other issues that have been detected from previous iMIS upgrades.

Note: This script is only compatible with iMIS 2017.

Script details

Review the following important details about the ValidateDocumentSystem2017GA.sql script:

• Script location: The latest version of the script is here: Validate Document System Script
• Returned information: This script will check the document system sort orders, depths, and hierarchy. If there are no issues, the script will return zero rows. If there are issues, the query will return rows for each issue separately.
• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading to ensure the database is as clean as possible before upgrading to EMS.

Fixing the issues returned by the script

Use the FixDocumentSystemCommonIssues.sql script to automatically fix most issues found by the ValidateDocumentSystem2017GA.sql script.

Next steps

After fixing the issues by using and running the FixDocumentSystemCommonIssues.sql script, run the ValidateDocumentSystem2017GA.sql script again. There may still be rows returned, because some issues, such as the duplicated hierarchy errors, require manually scripting fixes specific to the database.

If there are still rows returned by the script, you will need to enter an ASI Technical Support ticket, so that you can get assistance from ASI.

The ValidateMANAGERUser.sql script identifies any issues causing the MANAGER account to become corrupt or any missing permissions that cause the MANAGER user to malfunction.

Script details

Review the following important details about the ValidateMANAGERUser.sql script:

• Script location: The latest version of the script is here: Validate MANAGER User Script
• Returned information: If the MANAGER account is okay, it says so. If there are any issues, it will list all related MANAGER contact rows from various database tables.
• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading to iMIS EMS to avoid issues with the MANAGER account.

Fixing issues returned by the script

Use the FixManagerUser.sql script to automatically fix the MANAGER record for any issues found by the ValidateMANAGERUser.sql script. The FixManagerUser.sql script will not create a new MANAGER record if it is completely missing.

This script is safe to run on any database. If there is nothing to fix, it fixes nothing.

Next steps

After fixing the issues by using and running the FixManagerUser.sql script, run the ValidateMANAGERUser.sql script again to ensure there are no more issues. The bottom of the validation script will show there are no more issues if none are found.

The ValidateAddresses.sql script validates contact addresses.

Script details

Review the following important details about the ValidateAddresses.sql script:

• Script location: The latest version of the script is here: Validate Address Script
• Returned information: This script will check for orphaned rows, duplicate address tabs, invalid address pointers, and more.
• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading to iMIS EMS to avoid address related issues.

Fixing issues returned by the script

Use the FixOrphanedAndDuplicateAddresses.sql script (attached to Validate Address Script) to automatically fix issues found by the ValidateAddresses.sql script.

The  FixOrphanedAndDuplicateAddresses.sql script fixes orphaned address rows, invalid address pointers, and many (but not all) cases of duplicated address tabs. This script is safe to run against any database.

Next steps

After fixing the issues by using and running the FixOrphanedAndDuplicateAddresses.sql script, run the ValidateAddresses.sql script again to ensure there are no more issues. If there are additional issues found that the fix script cannot correct, enter a ticket with ASI Technical Support.