Upgrade Scripts

The ListNonImisDatabaseObjectName script, also referred to as the Customizations script, determines how many customizations are in the database and creates the script to remove the customizations.

Script details

Review the following important details about the ListNonImisDatabaseObjectNames script:

• Script location: The latest version of the script is always located in the Support Portal - ListNonImisDatabaseObjectNames
• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading or on an iMIS EMS site during the test phase.

Note: If the database is on an earlier version, you must take a backup of the database, upgrade the backup to iMIS 2017, then run the script.

• Returned information: This script returns custom tables, views, stored procedures, and functions.
• Information not included: iMIS Customizer tables are not considered a customization. This script does not identify customizations outside of the database, such as custom iParts.
• Confirm the objects are removed: Running this script is iterative. After dropping unreferenced objects, run the script again to see if new objects appear to be unreferenced.
• Script run time: Each run takes between two-to-five minutes, depending on the size of the database and how busy the server is.

Using the script

When the script is run against a specific database, there are six different result sets:

Result Set 1: Script version

The version of the script. Be sure you are always using the latest version.

Result Set 2: Database summary

A single row of information about the database. This includes the database name, iMIS version, and the number of identified non-iMIS tables, views, stored procedures, functions, and triggers.

Result Set 3: List of all customizations

A list of all customizations, including the type (e.g., view, table) and name of the object. This tab also contains the SQL that will drop the custom objects from the database.

Note: Do not run the SQL to drop the customizations before the objects have been reviewed (Result Set 5).

Result Set 4: List of customizations not being referenced

A list of all customizations that are not being referenced anywhere in the database, which means they are typically safe to drop without needing to replace them.

Before upgrading live, drop these customizations.

Result Set 5: List of customizations that are being referenced

A list of all customizations that are being referenced, including information about what is referencing the custom object.

Identifying which tables have custom triggers in use

After running the ListNonImisDatabaseObjectNames script,execute the following script to identify which tables have custom triggers in use. Use this script to filter out ASI-specific triggers, allowing you to focus exclusively on custom tables and their associated triggers.

1
2
3
4
5
6
7
8
9
10
11
--Identify what tables the triggers are on
SELECT [name] AS TriggerName, OBJECT_NAME(parent_id) AS TableName
  FROM sys.triggers
ORDER BY TableName;

--Filter out the ASI triggers 
SELECT [name] AS TriggerName, OBJECT_NAME(parent_id) AS TableName
  FROM sys.triggers
WHERE [name] NOT LIKE 'asi_%' ESCAPE '\'
   AND [name] != 'Users_Insert_Update_Delete'
ORDER BY TableName;

(Important) Required items to complete before upgrading live

Before the live upgrade, complete the following:

1. Discuss and analyze the results of the scripts. Determine which customizations are needed, how to replace them, and any other details surrounding the customizations.

Tip! Color code the output document to easily see which are actively being used (GREEN), which you are unsure about (YELLOW), and which are not being used anymore (RED).



2. If the customization is not required to keep, run the related SQL to drop it from the database.
3. Replace all required customizations using out-of-the-box iMIS.
4. Once replaced, update all areas using the custom object with the new out-of-the-box object. This step should be performed in the test site, so you can test and confirm all replacements are properly working.

Example: A custom view is replaced using Expression Builder. The custom view was being used with a Business Object (BO), which was being used in many queries. All queries must be updated with the new BO, so they can be tested prior to upgrading. To easily find queries where BOs are being used, see Finding queries that use a specific business object.

5. Drop the object using the provided SQL.

(Important) Required items to complete after upgrading live

After the live upgrade, complete the following:

1. Export the out-of-the-box objects from the test site and import them to the live site.
2. Manually update any remaining items that are using the new objects, such as panels, process automation tasks, and anything else that cannot be exported from the test site.

Result Set 6: Business Objects (BO) that must be dropped prior to upgrade

A list of BOs that will not properly upgrade, because they reference tables or views that do not exist or are being removed.

(Important) Required items to complete before upgrading live

Before the live upgrade, complete the following:

1. Determine if the BO is required to keep. If you are not sure where the BO is being used, use the object reference name (Column B in the list of results) with the following information: Finding queries that use a specific Business Object. Alternatively, use the Query Browser Utility to assist you with this step.
2. Create a replacement BO in the test site using out-of-the-box iMIS.
3. In the test site, update queries identified in step 1 with new BO from step 2.
4. Export the replacement BO and related queries from the test site.
5. Drop the BO from the iMIS 2017 database (Column A in the list of results).

(Important) Required items to complete after upgrading live

After the live upgrade, complete the following:

1. Import BOs and queries to the live site.
2. Delete any old queries no longer needed due to the imported replacement queries.
3. Run queries in the live site to confirm they are properly working.
4. Update all areas that were using an old or deleted query, such as content records or Process Automation tasks.

This script identifies additional areas of complexity that may require further investigation prior to upgrading.

Script details

Review the following important details about the Additional Complexities script:

• Script location: The latest version of the script is always located in the Support Portal - Additional Complexities
• Returned information: The script identifies items in a database that typically take more time to analyze and replace, such as the following:
  • Billing special pricing rules - Most special pricing rules cannot be edited in a billing product after an upgrade. These rules can be time consuming to replace, so we consider it an additional complexity to consider before upgrading. See Converting custom multi-year billing for more information.
  • Custom Crystal Reports - Crystal Reports are no longer supported, so this result set returns a list of all identified Crystal Reports. Collaborate with the client to determine if a report is still needed. If so, determine if one of the new out-of-the-box reports or a query can be used as a replacement.
  • Source Codes to Migrate - iMIS EMS handles fundraising source codes differently, and there is a manual process involved pre-upgrade. See Migrating campaigns and appeals from Desktop to EMS.

  Note: Review the ReadMe located in the Additional Complexities zip file for the full list.

• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading.

Using the script

The original inception the Additional Complexities script was the result of a need to identify how a client has used iMIS throughout the lifetime of their iMIS database. Combined with the results of the ListNonImisDatabaseObjectNames script, this script's results can provide a more complete picture.

This script can also be used to identify features/areas currently being utilized by a client but may no longer be available once they upgrade. After the results are analyzed, the next steps should be clear; for example, if it returns that a client has many special pricing rules, you should begin replacing those rules with out-of-the-box iMIS.

After running the ListNonImisDatabaseObjectNames and Additional Complexities scripts and dropping the customizations, run the RefreshViewMetadata script.

Script details

Review the following important details about the Refresh Meta Data script:

• Script location: The latest version of the script is always located in the Support Portal - RefreshsViewMetadata
• Returned information: This script will check for any broken views, views that are referencing dropped tables, or other dropped views.
• When and where to run the script: Run the script against the iMIS 2017 database prior to upgrading but only after dropping the customizations returned from the ListNonImisDatabaseObjectNames and Additional Complexities scripts.

Using the script

The Refresh Meta Data script may return objects that are newly broken due to the customizations that were dropped after running the ListNonImisDatabaseObjectNames and Additional Complexities scripts. If results are returned, continue the analysis on the identified objects to determine if the object must be replaced. If so, use out-of-the-box iMIS to replace the object.

This script identifies customizations that reference schema that became obsolete between iMIS 2017 and iMIS EMS.

Script details

Review the following important details about the ListCustomizationsThatReferenceObsoleteSchema script:

• Script location: The latest version of the script is always located on the Support Portal - ListCustomizationsThatReferenceObsoleteSchema
• Returned information: After running the ListNonImisDatabaseObjectNames and Additional Complexities scripts and dropping the customizations, run this script. This script will check for any broken views, views that are referencing dropped tables, or other dropped views.
• When and where to run the script: Anyone upgrading from iMIS 2017 to iMIS EMS (on-prem, 20/20 Advance, and ASI-hosted) should run these scripts on their iMIS 2017 database before upgrading.
• Understanding the change:
  • Stored procedures and functions: To conform to ANSI and SQL best practices, the "fn_" and "sp_" prefixes were dropped from functions and stored procedures. Additionally, for any function or stored procedure that was missing the "asi_" prefix, a new version was created that has the "asi_" prefix.
  • Panels: To improve performance for sites with large amounts of data, all user-defined tables (created with iMIS Customizer) have been converted to v3 panel sources that can easily be used with Panel Designer.

Using the script

Review the following to understand how to use this script:

• Stored procedures and functions:
  • The script identifies customizations (non-iMIS functions, stored procedures, views, and triggers) that reference the iMIS-specific stored procedures and functions that have been renamed in iMIS EMS.
  • Before you upgrade, integrations that reference the renamed stored procedures and functions must be updated to reference the new names. If there are results, the stored procedure or function name can be updated by removing the "fn_" or "sp_" prefixes. The script returns the old and new names, so you know what to update the names to.

  Example: The iMIS 2017 name of a stored procedure is sp_asi_FullAddress. The iMIS EMS name of a stored procedure is asi_FullAddress.

• UD Panels:
  • The following tables are obsolete in iMIS EMS:
    • UD_Table
    • UD_Field
    • UD_WindowHeader
    • UD_WindowFields
    • URL_Mapping
    • User_Defined_Content_Labels
    • User_Defined_Field
    • UserDefinedMultiInstanceProperty
    • UserDefinedSingleInstanceProperty
    • UserDefinedTableStorage
  • Customizations should not reference any of the above tables. If there are customizations that reference the above tables, the script will return those results. Additionally, customizations should not directly reference the views built over the above tables, such as views that are prefixed with "vUDBO" or "vUDMBO". Instead, directly reference the related "vBO" view.

Important! Only those upgrading on-premise or through the iMIS 20/20 Advance Program should run this script.

Script details

Review the following important details about the ListCustomizationsNoLongerSupported script:

• Script location: The latest version of the script is always located on the Support Portal - ListCustomizationsNoLongerSupported
• Returned information: Returns references to functions and stored procedures that are not supported in iMIS EMS.
• When and where to run the script: Anyone upgrading from iMIS 2017 to iMIS EMS through the iMIS 20/20 Advance Program or self-hosted should run these scripts on their iMIS 2017 database before upgrading.

Using the script

The ListCustomizationsNoLongerSupported script will return references to functions and stored procedures that are not supported in iMIS EMS. If results are returned, continue the analysis on the identified objects to determine if the object must be replaced. If so, use out-of-the-box iMIS to replace the object.