How to configure the SQL Server database configuration for Task Processor in Confirm

Prerequisite: Enable SQL Server Agent from the SQL Server Configuration Manager. The SQL Server Configuration Manager is installed with the SQL Server and can be opened from Microsoft SQL Server -> Configuration Tools in the Start Menu.

The screen shot shows from where to enable the SQL Server Agent in Microsoft SQL Server 2012:

User-added image

Step 1: Creating the Script.
  • Copy the Task Processor - Database Configuration folder to the local drive from Resources folder within Confirm media.  Ensure following files exist in the same folder:
  1. Create Database Script.bat 
  2. MSSQL File1.txt 
  3. MSSQL File2.txt 
  4. MSSQL File3.tx
  • Launch 'Create Database Script.bat' batch file. 
  • Enter 2 when prompted for the type of database. 
  • Enter Confirm Database name when prompted. 
  • Enter Schema Name in Uppercase. Each dataset of Confirm corresponds to a Schema in the database. 
  • Enter Schema Login Name (schema/asset owner) that corresponds to Schema. 
  • Repeat above two steps if you have more than one Confirm dataset installed. 
  • The script is not to be run against SBS schema. 
For example: How the entries may look like:

User-added image

The batch file will create an SQL script named '_Output Database Script.sql' in the location where the batch file exists.

Note: For Microsoft SQL Server versions from 2008SP1 to 2008SP3, please contact Pitney Bowes Support for database scripts.

Step 2: Running the Script.
  • Login into the database with DBA permissions. 
  • Open script '_Output Database Script.sql'. 
  • Run the script. In case more than one connections are open, you'll get the following error: "Detected existing connections. Ensure all other connections are dropped before executing this script." To resolve, close all existing connections and run the script again. 
  • Logout from the database. 
The scripts will create following jobs which need to be enabled from within the database.
  • job_TaskMaintenance_<Database Name>_<SCHEMA NAME> 
  • job_TaskScheduler_<Database Name>_<SCHEMA NAME>
The screen shot is a sample representation of the entities created by the MSSQL Script:

User-added image

Once the database configuration is complete Task Processor needs to be setup.

UPDATED:  October 26, 2017