Setting up GainSeeker on a SQL Server database

Your SQL Server database administrator (DBA) must perform the following steps on the SQL Server:

1. Use SQL Server Management Studio or another tool to manually create a new GainSeeker database. You can use default or typical settings for a new database, except for the following:

• Database name: The recommended name for the new database is SPC. (If desired, you may enter a different name.)

• Collation: You must set the Collation to either SQL_Latin1_General_CP1_CI_AS (recommended, CI = case insensitive) or SQL_Latin1_General_CP1_CS_AS (CS = case sensitive).
  If you choose the case sensitive option, all table names and field names will also be case sensitive.
  In SQL Server Management Studio, while creating the new database, click the Options page to find this setting.

• Auto Create Statistics: It is recommended that you set the Auto Create Statistics setting to True.
  In SQL Server Management Studio, while creating the new database, click the Options page to find this setting.

2. If using SQL Server authentication (recommended), create a SQL Server Login (user) for this GainSeeker database. For this new user:

   1. Set the Default Database to the GainSeeker database.
      In SQL Server Management Studio, while creating the new Login, use the General page to find this setting.

   2. For the GainSeeker database, grant the roles of public and db_owner to this user.
      In SQL Server Management Studio, while creating the new Login, use the User Mapping page to find this setting.

3. Create all necessary tables in the GainSeeker database. Scripts for creating and populating the necessary tables are provided in the GainSeeker installation package:

   • Locate the script SPC8_SS.SQL in the \Scripts\SQLSVR\ folder.

   • If you plan to use Expanded data fields, edit the script and update the variables @DBPSD, @DBPD, and @DBUDL to the desired field lengths.

   • If you plan to use Additional traceability columns, edit the script and add columns to the DDAT_AUX, VDAT_AUX, and VMON_AUX tables (see comments in the script file)..

4. Ensure that statistics will be updated regularly for this database.

5. Ensure that SET NOCOUNT is OFF for the GainSeeker database by running the query  SET NOCOUNT OFF  for this database. (If SET NOCOUNT is ON, GainSeeker will display error messages and fail to store some types of data records.) For more information, see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql.

After setting up the database, you can install the GainSeeker client on each workstation.

• The first GainSeeker client installation will prompt you to enter the SQL Server name, choose Windows Authentication or SQL Server Authentication, specify the SQL Server Login ID and Password (if using SQL Server Authentication), and choose the name of the GainSeeker database (catalog).

• 1. If you edited the script for expanded data fields or additional traceability columns, you must use the GainSeeker System Administration module to set the field lengths or additional traceability columns on the Initial Configuration:

  2.  

  3. Go to the settings for Tables and File Paths and click the Expand column length and/or the Additional Traceability Columns button and set the appropriate values.