If you have a database that doesn't have the latest schema you need to execute a series of scripts to update it. If this isn't done the database won't work correctly.
An output parameter (@trackID) is added to the insert_tracking_point_pass stored procedure and this will possibly break some custom stored procedures.
Also, any custom stored procedures that insert data into the MUI table will need to be reworked since unit_id is now an identity/sequence column.
This section assumes your Inspect database schema is at level 5.0.02 or higher. If you have a schema prior to level 5.0.02, please contact ATS Inspect Technical Support.
The following list shows the general steps required to update the schema:
1. Backup the existing database
2. Determine your current database schema
3. Run the update scripts.
4. Run the Database Password Encryption utility
Before beginning to execute any of the upgrade scripts, it is very important for you to backup your existing database. Ensure that your backups are valid and complete before continuing to the next step. Upgrading a copy of your production database in a test environment and thoroughly checking it is always recommended.
To determine your current database schema, login as the database owner and execute the following SQL statement in Microsoft SQL Server Manager Studio:
SELECT SCHEMA_MAJOR_VERSION, SCHEMA_MINOR_VERSION, SCHEMA_BUILD_NUMBER FROM GLOBAL_SETTINGS
The database version can also be found from the Admin program, under Configuration > Settings > Global.
Once you know your current database schema level, you can determine which script you need to begin with for the update process.
For example, if your database were currently on schema 5.2.00, you would first execute the update_5_2_00_to_5_3_00.sql script, then the update_5_3_00_to_5_4_00.sql script and so on as described until your database is updated to very latest version.
Once each script has completed, verify that no errors were generated. If errors occurred, you will need to fix the indicated issue(s) before continuing with the next script.
The upgrade scripts are located in the following directory given below. If desired, you may copy the scripts from this location to one that is more convenient for you to use.
C:\Program Files\Applied Tech Systems\Inspect\Connectivity Kit\Update
Always run the scripts from the current database schema level to the latest schema level, unless directed to do otherwise by ATS Inspect Technical Support.
The Password Encryption Wizard must be run whenever a database from Inspect 5.4.0 or earlier is updated.
For further information see here.
The Data Transfer Wizard enables an administrator to convert a database for use with unicode. It also enables Oracle databases created before version 5.5 to be used with version 5.5 and beyond.
For further information see here.
The following options must be turned on for the database in SQL Management Studio. Right click on the database and select Options:
● SET ANSI NULLS = ON
● SET QUOTED IDENTIFIERS = ON
The database compatibility level must also be at either 90 for MSSQL 2005 or 100 for MSSQL 2008. If it is not, run the following query against the database (in a query window):
If you are using 2005
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
If you are using 2008
EXEC sp_dbcmptlevel DatabaseName, 100;
GO