Intermittent Update Failures in MicroStrategy Transactional Services with Large Numeric Values

Symptom

When utilizing Strategy Transactional Services to update database records through a simple document, users may encounter sporadic failures. The issue does not affect all records uniformly; updates are successful for some records while failing for others without a seemingly clear pattern.

Cause Upon reviewing the DSSErrors.log file, the following entries are observed:

1. [MSICmdUtilXMLReportExec.cpp:3066] [F32331] Failure to retrieve the original document instance, reverting to original logic.
2. [MsiWorkingSetAux.cpp:7537] [F29196] Unable to build reverse delta, current command: rfrs, stid: -1
3. RWDCache Manager:LookupCache: Disabling caching for EnumDSSExportFormat: 'Unknown Format' for document GUID: xxx

Further investigation, enabled by activating the SQL Trace log, reveals that the issue arises when the Transaction Input parameters include a numeric data type with a length greater than 9 digits. The generated SQL update statement's WHERE clause incorrectly contains the value -2147483648. This number, which is the lowest value for a 32-bit signed integer, indicates an overflow issue.
 

Solution

To resolve this technical challenge, two primary solutions are available:

1. Refer to the Knowledge Base article KB46983
2. Modify the Transaction's Freeform SQL definition directly:

a. Navigate to the Transaction settings and select the 'Data' option.
b. Access the Freeform SQL definition and locate the input objects.
c. Remove the column associated with the numeric values that are not updating correctly.

d. Then redefine a new input in place of the old one, but as the datatype, do not just the Number, but rather “Big Decimal”.

e. Save the transaction and update schema. After those changes are reflected, the issue should be solved.
Save the transaction and update schema. After those changes are reflected, the issue should be solved.

By implementing either of these solutions, users should be able to successfully update records with large numeric values using Strategy Transactional Services without encountering the previously noted failures.