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:

   • If you plan to use the typical GainSeeker table structures, use the SPC8_SS.SQL script in the \Scripts\SQLSVR\ folder.

   • If you plan to use Expanded data fields, use the SPC8_SS.SQL script in the \Scripts\SQLSVR\Expanded\ folder and update the variables @DBPSD, @DBPD, and @DBUDL to the desired field lengths.

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).

• If you used the script for expanded data fields, you must use the GainSeeker System Administration module to set the field lengths for the Initial Configuration:

• 1. Go to the settings for Tables and File Paths and click the Expand button. This will display the Expand Column Lengths window with default column lengths.

  2. Set the three column lengths to match the three variables in the script you executed, then click OK.