VERIFIED SOLUTION i

Transfers stuck on Officer Location tasks and "An internal ConfirmConnect service error occurred" message is in ConfirmConnect

Products affected: Confirm®

Issue

Officer location seed numbers are used for GPS tracking on ConfirmConnect and Confirm Mobile and may run out. This means the Action Officer location data cannot be uploaded from the device.

Users can log in to ConfirmConnect but users in some Work Groups cannot upload Tasks that are displayed on the Task tab.  A generic error message is displayed which says,
"Messages are not currently being sent
Reason: An internal ConfirmConnect service error occurred Trying again in 159 seconds"

The first entry on the list on the Transfer tab is Officer Location.

User-added image

In ConfirmConnect versions prior to 5.20 users may see one of the following error messages:
 
"Not enough Seed Numbers remaining for Work Group (INSP) for OFFICER_LOCATION"
"Officer Location Transfers are not transferring"


In Confirm v19.00 or later the ErrorMS_Connector.log file contains:

System.Data.OracleClient.OracleException (0x80131938): ORA-00001: unique constraint (XXXX_SBS.OFFICER_LOCATION_PKX) violated

Cause

The range of seed numbers for officer location for the user's Work Group has run out.

If you are using a SQL Server database or an Oracle one, where Confirm is before version 19.00, use this SELECT query to determine whether you have run out of seed numbers:

SELECT work_group_code, seed_type_code, seed_number, seed_max, next_seed_number, next_seed_max
FROM work_group_seed
WHERE seed_type_code = 'OFFICER_LOCATION';


These results show that in this example seed numbers have run out because the seed_num value is one less than the seed_max value.

work_group_code  seed_type_code   seed_num  seed_max next_seed_number   next_seed_max
ALL              OFFICER_LOCATION 99999     100000   0                  0



If the Confirm database is an Oracle one and is on version 19.00 or later, the Officer Location seed number is taken from a sequence in the database.  If the sequence is out of sync with the maximum officer_location.location_key, Officer Location transfers will not upload.

Resolution

UPDATED: March 15, 2019
This issue where the work_group_seed table cannot assign the next seed number is a bug (CNB-66948) which is fixed in Confirm version 19.00.  In earlier versions Confirm Technical Support can provided SQL to extend the seed number range.

For Confirm version 19.00 or later on an Oracle database run this SQL to find the sequence value for Officer Location and the maximum officer_location.location_key.
 
SELECT max(location_key) FROM officer_location;
SELECT SEQUENCE_OFFICER_LOCATION.NEXTVAL FROM DUAL;

Send the results of the two SQL statements to Confirm Technical Support.
Note: Running the NEXTVAL script updates the sequence by one even though it is a SELECT statement.

Work around
Delete the Officer Location entries on the Transfer tab of ConfirmConnect and other transfers will go through.