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.
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:
Backup the existing database
Determine your current database schema
Run the update scripts.
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 Cockpit program, under Management > 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.
There may be instances where the version of your database falls within the range of the update script i.e. Your database version is 5.2.1.0. If this occurs, then you will need to edit the relevant script (in this example the update_5_2_00_to_5_3_00.sql script). Search for the database version you are on and delete everything above this line before running the script.
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 Data Transfer Wizard enables an administrator to convert a database for use with unicode.
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
Can we improve this topic?