ANSI Conversion Utility

iMIS Desktop requires ANSI settings to be enabled at all times. Whenever Desktop creates a connection (ODBC or otherwise), it does so with all ANSI options turned ON. To ensure that that the entire iMIS database has the correct ANSI settings, the upgrade utility rebuilds all tables and recreates all stored procedures, triggers, functions, and views.

📘
Note
Because of this extensive rebuilding, expect upgrading to be significantly slower for very large databases. Before upgrading, check that you have ample free disk space relative to the size of your database, as these tables will be copied during the process of conversion and rebuilding.

ANSI settings required by iMIS

iMIS requires that all tables be fully ANSI compliant, so they must be created with SET ANSI_NULLS ON and SET ANSI_PADDING ON. The ConvertTablesToANSI command-line utility rebuilds every table in the database that is not ANSI compliant, including user-defined and third-party tables.

If you are creating your own tables, procedures, triggers, views, and so forth, be sure to set these ANSI settings:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

Running a standalone ANSI conversion

The DB Upgrader runs the ConvertTablesToANSI utility as part of an iMIS upgrade, but you can run it standalone as well, from the command line. To ensure a smooth upgrade for a large and complex database, you can do a pre-upgrade conversion on a backup of the database to anticipate problems.

Create a backup, an off-line copy of your database.
If you haven’t upgraded, run the iMIS installer to expand copies of the database utilities.
Locate the ConvertTablesToANSI utility, in the C:\Program Files (x86)\ASI\iMIS\Net\iMISDBUpgrade\Utilities folder.
From the command line, run the utility against your database backup:

                        ConvertTablesToANSI.exe -S sqlServerName -U sqlUser -P sqlPassword -D dbName

If your database copy converts with no errors, you can use this converted database for your upgrade, which saves the time of reconverting. If there is an error, contact Support for assistance before upgrading.

Troubleshooting ANSI Conversion errors

The ANSI conversion utility rebuilds every table that is not ANSI compliant. These tables are listed in the upgrade log:

Rebuilding Tables For Full ANSI Compliance ...
   Converting Table 1 of 89: Activity
   …
   Converting Table 88 of 89: UserMain
   Converting Table 89 of 89: W_Reports
   ANSI Conversion Successful
Step 4 Done.

The total number of tables rebuilt varies by database. Any errors appear the build log, flagged with three asterisks (***), before both the description of the problem and the end of the step:

   Converting Table 219 of 234: UsedSourceCode
   *** Unable to copy data into table UsedSourceCode
   …
   Converting Table 234 of 234: tempTypeNamesList
   *** Errors during ANSI Conversion
Step 4 Done.

After such an error, the upgrade continues. Although the completed upgrade reports being failed, the upgrade will be valid as long as there are no other errors. When a table fails to convert, the utility restores it to its original, unconverted condition, unless connectivity to the database is lost.