Creating a New Database Schema

The Inspect Connectivity Kit must be installed before attempting to create a database.

Follow the instructions in this section to create a new database schema using the Connectivity Kit.

The Microsoft SQL Server database build procedures are intended for the database administrator to build the Inspect database schema on the database server.

Specific database build procedures vary, depending on the tools used by the database administrator. This documentation provides only general procedures.

The following list outlines the required steps for the update:

Copy the build script files to the database server.

Edit the InspectBuild.cmd script.

Execute the InspectBuild.cmd script in the command prompt.

Verify the build process.

Update to the latest database schema.

Step 1: Copy the Contents of the Build Directory

In order to build the database the required files must be on the machine holding the database server. If Inspect has not been installed on the database server machine the files must be copied to it.

After installing the Connectivity Kit the build files can be found, by default, in the following directory:

C:\Program Files\Applied Tech Systems\Inspect\Connectivity Kit\Build

Copy this directory to the database server before beginning the following procedures.

Step 2: InspectBuild.cmd

The InspectBuild.cmd file is read only and must be unlocked before you can modify it.

Use a text editor such as Notepad to edit the InspectBuild.cmd script, the main command file that calls the other SQL files to build the database. The InspectBuild.cmd file contains the following default values:

set dbserver=<database server name>

set dbname=<database name>

set username=<user name>

set password=<password>

Modify them as required.

Make sure that the "sa" login password is modified to reflect the correct "sa" password of the database server.

The InspectBuild.cmd file executes several files using the Microsoft SQL Server OSQL utility.

DO NOT make modifications to these files:

Filename	Description
01 - CreateDatabase.sql	Creates a new database on the server
02 - CreateRolesAndUsers.sql	Creates a new 'db_executor' role which allows execute permissions for all stored procedures. Creates two logins and database users.
03 - CreateBasicSchema.sql	Creates all database tables and related objects like constraints, foreign keys, indexes, etc.
04 - CreateSchemaBinding.sql	Create XML Schema collections and bind to appropriate table columns.
05 - CreateTypes.sql	Creates user defined data types.
06 - CreateViews.sql	Creates database views
07 - CreateFunctions.sql	Creates all Inspect database functions.
08 - CreateStoredProcedures.sql	Creates all Inspect database stored procedures.
09 - CreateTriggers.sql	Creates all Inspect database triggers.
10 - InsertLookupValues.sql	Loads lookup data into the database.
11 - CreateFormsAndControls.sql	Creates forms and controls in the database.
12 - ScheduledJobs	Creates jobs that help manage the Inspect database.
13 - VerifyDatabase.sql	Verifies the database creation by checking the counts of all major objects in the database.

Step 3: Execute the Database Build Script

Execute the script by typing InspectBuild.cmd at the command prompt on the server.

If the user running the InspectBuild.cmd script is different from the user that ran the ATS Inspect installer, you might run into errors. For further information see here.

If you are using Windows Vista or 7 you must start the command prompt with administrative rights.

The InspectBuild.cmd script does the following:

Creates a subdirectory named \logs. If a \logs directory exists, it deletes all old *.err and *.out files from the logs subdirectory.

Retrieves the version number of the database and the time from the database.

Creates all database objects like Tables, Triggers, Stored Procedures, Indexes, Foreign Keys, etc.

Inserts default data and manages permission on all tables.

Runs the database verification script.

Step 4: Verify the Build Process

Change the directory to the logs directory and make sure that there are no *.err files.

Compare the counts in the DatabaseVerify.out file and make sure they match (each count should be duplicated).

If the counts value does not match, then review the *.out file(s) and execute the appropriate step(s) to resolve the issue(s).

Step 5: Update to latest Schema

Now that the database has been created it may need to be updated to the latest schema.

For information on how to do this see here.

Step 6: Modify Licensing Endpoint

In order for the database to access licenses the licensing endpoint may need to be modified.

Go to the global_settings table in the database and modify wcf_license_svc_endpoint.

The Licensing Service must be restarted if the endpoint is modified.

If you are using a Proxy server and client applications can't connect to the licensing server using the default http connection method the licensing endpoint should be changed to use net.tcp.
e.g. net.tcp://ServerIP:8001/ATSLicensingServer/

Modifying the Default Language

When a database is created the default language is English. This can be changed by modifying the default_language_id entry in the global_settings table. This must be done before any data is entered in the database otherwise issues may occur.

Using the Database

Two standard logins are created for a new database, as follows:

Username: inspect

Password: Quality2010

Username: ekutay

Password: Quality2010