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:
1. Copy the build script files to the database server.
2. Edit the required files.
3. Execute the InspectBuild.cmd script.
4. Verify the build process.
5. Update to the latest database schema.
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.
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:
● Server: localhost
● User name: sa
● System Administrator (sa) Password: master
● Database Name: INSPECT
Modify them as required. They will be shown as follows:
sqlcmd -S localhost -U sa -P master -d inspect -iCreateRoles.sql -o logs\CreateRoles.out
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.
Modify the following two files as required:
If you change the database name in the InspectBuild.cmd file you will need to change the database name in two places in this file.
Make sure that the following database path exists and that there is enough disk space to create the database.
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
The database name is shown twice. Be sure to modify both of them:
create database <database_name>...
alter database <database_name> ...
By default, the CreateDatabase.sql file creates the empty database schema and contains the following default values from the InspectBuild.cmd file:
The database name (INSPECT)
The data file (mdf: 800MB)
The log file (ldf: 100MB)
Sets the database owner to Inspect. Change this file to change the database owner to a different login.
InspectBuild.cmd also runs the contents of the following files.
DO NOT make modifications to these files:
| Filename | Description |
| 02 - CreateRoles.sql |
Creates a new 'db_executor' role which allows execute permissions for all stored procedures. |
| 03 - CreateDatabaseUsers.sql |
Creates two logins and database users. |
| 04 - CreateBasicSchema.sql |
Creates all database tables and related objects like constraints, foreign keys, indexes, etc. |
| 05 - CreateSequenceTables.sql |
|
| 06 - CreateFunctions.sql |
Creates all Inspect database functions. |
| 07 - CreateStoredProcedures.sql |
Creates all Inspect database stored procedures. |
| 08 - CreateTriggers.sql |
Creates all Inspect database triggers. |
| 09 - CreateLookupValues.sql |
Loads lookup data into the database. |
| 10 - CreateApplicationUsers.sql |
Creates two 'default' application users and gives them super user privileges. The user names and passwords match the SQL Server logins created by the CreateLoginsAndUsers.sql script. |
| 12 - DatabaseVerify.sql |
Verifies the database creation by checking the counts of all major objects in the database. |
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 \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.
1. Change the directory to the logs directory and make sure that there are no *.err files.
2. Compare the counts in the DatabaseVerify.out file and make sure they match (each count should be duplicated).
3. If the counts value does not match, then review the *.out file(s) and execute the appropriate step(s) to resolve the issue(s).
Now that the database has been created it must be updated to the latest schema.
For information on how to do this see here.
Two standard logins are created for a new database, as follows:
Username: inspect
Password: Quality2010
Username: ekutay
Password: Quality2010