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:
1. Copy the build script files to the database server.
2. Edit the InspectBuild.cmd script.
3. Execute the InspectBuild.cmd script in the command prompt.
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\ATS 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:
● 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.
Modify the following file if required:
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 |
01 - CreateDatabase.sql |
Creates a new database on the server |
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 - 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 - 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. |
14 - AddAndBindSchemas.sql |
Adds and binds schemas |
15 - VerifyDatabase.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 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.
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 may need to be updated to the latest schema.
For information on how to do this see here.
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.
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.
Two standard logins are created for a new database, as follows:
Username: inspect
Password: Quality2010
Username: ekutay
Password: Quality2010