SA-10433

Summary

User is running a BIX extraction that has been run many times before without issue.  This time it fails with a BatchUpdateException. 

Error Messages

ERROR   - Batch Update Exception: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
java.sql.BatchUpdateException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Steps to Reproduce

Not Applicable.

Root Cause

The root cause of this problem is a defect in customer application code/rules.

Committing an extract batch of class instances to a relational database may be interrupted because of issues with the data, syntax errors, a database constraint violation, or some other issue raised by the database.

 Different databases respond differently to batch update exceptions. However, in general the database throws a BatchUpdateException which BIX records in the BIX log file along with the pzInskey values of all of the Work Objects (WOs) in the batch.  

One or more of these WOs cannot be inserted and as a result the entire batch was rolled back.  From the error message one can see in this case the problem is with the conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.  

Resolution

The explanation for this behavior is as follows: 

From the log file one can see a list of the pzInsKeys for the WOs in the failed batch.  

​Using a technique such as the -z/-Z parameters as in this article user require to determine the WO(s) that have a property value which cannot be inserted into the database column and correct it. Besides correcting the data in the problem WOs one must  also look at updating any flows which allowed users to enter data in an invalid format.

Reference PDN Article that explains using -z/-Z parameters:
https://pdn.pega.com/system-operations/how-to-filter-pzinskey-for-a-range-of-values-using-prpcutils-and-bix