Support Article

CSHC 722 Sample Data Install Fail

SA-37630

Summary



While installing SampleDB\CSHCSample.jar, it is throwing data type mismatch error.


Error Messages



2017-05-01 07:11:50,983 [ PegaRULES-Batch-3] [ STANDARD] [ ] [ CSHC:07.22] ( internal.util.MoveLog) INFO CSHCSysAdmin - Error: Save Failed: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
DatabaseException caused by prior exception: java.sql.BatchUpdateException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
| SQL Code: 242 | SQL State: S0003
(PegaCPMHC-Interface-Address MB2016051137)
2017-05-01 07:11:50,993 [ PegaRULES-Batch-3] [ STANDARD] [ ] [ CSHC:07.22] ( internal.loaders.SaverLoader) ERROR CSHCSysAdmin - Save failed for 'PegaCPMHC-Interface-Address MB2016051137'
com.pega.pegarules.pub.database.DatabaseException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
DatabaseException caused by prior exception: java.sql.BatchUpdateException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
| SQL Code: 242 | SQL State: S0003


Steps to Reproduce



While installing SampleDB\CSHCSample.jar, it is throwing data type mismatch error.


Root Cause



A defect or configuration issue in the operating environment. There are 3 instances of PegaCPMHC-Interface-Address which fail to import because the data trying to be added to the DateAdded column is outside of the range allowed for a datetime column. DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

Resolution



Perform the following local-change:

If you require the 3 problem instances, and have not tried to import this jar already,
  1. Start import of CSHCSample.jar RAP and manually download DDL
  2. Alter create statement in pa_address table to use datetime2 column for DateAdded column and apply DDL
  3. Import RAP
If you require the 3 problem instances, but have already tried to import this jar,
1. Drop the primary key on pa_address table, alter the DateAdded column from datetime to datetime2, add the primary key back.
2. Import the RAP selecting the “Enable advance mode” checkbox and deselect everything except for the 3 rows that failed to import the first time.

PegaCPMHC-Interface-Address MB2016051137
PegaCPMHC-Interface-Address MB201608261512
PegaCPMHC-Interface-Address MB201608261513

Published May 8, 2017 - Updated May 10, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.