Support Article
Migrate script fails to convert Varchar data type to Numeric
Summary
Running the Migrate script fails to migrate the upgraded rules tables to the new schema. The Migrate script fails to convert Varchar data type to Numeric.
Error Messages
[java] (INFO): PegamoveData: SQL Statement Generated: Insert into Rules74.PR_INDEX_CIRCUMSTANCE_DEF ("pxCreateDateTime", "pxCreateOperator", "pxCreateOpName", "pxCreateSystemID", "pxIndexCount", "pxIndexPurpose", "pxIndexedClass", "pxInsIndexedClass", "pxInsIndexedKey", "pxInsName", "pxObjClass", "pxUpdateDateTime", "pxUpdateOperator", "pxUpdateOpName", "pxUpdateSystemID", "pyCountry", "pyCity", "pyDefinitionName", "pyTemplateName", "pyLabel", "pyRegion", "pyState", "pyChannel", "pyLevel", "pyCompany", "pyOrg", "pyDept", "pyPostalCode", "pzInsKey", "pyProjectMethodology", "pxCommitDateTime", "pxSaveDateTime", "CreateCaseClass", "ResponseType", "SurveySetup", "pyIsMobile", "CRPPropertyAliasClass", "CoverName", "CollectionType", "Mode", "pyFlowType", "CoverageCode", "ChannelID", "LocationState", "NegligenceLawName", "ClaimType", "PolicyIdentifier", "RepairLocation", "pyUserIdentifier", "SurveyMethod", "SearchPartyType", "AutoPayProcess", "WorkType", "SelectedQueueName", "UserBase", "CaseStatus", "DocumentCategory", "DocumentCategorySet", "OperatingHoursFlag", "InServiceFlag", "ServiceIntervalFlag", "SeverityFlag", "RequireService", "RequiresInService", "Country", "Department", "Type", "ReleaseType", "SelectedAOR", "AOR", "ComingFrom", "pyNote", "FlagVal", "pyProjectType", "ServiceVisitsFlag", "pxApplication", "ReportType", "CancelFeeRuleNumber", "ProcessFees", "FeeCode", "pySelected", "FeeCodeExist", "SuffixFormID", "FormID", "TakeAction", "pyWorkIDPrefix", "CalledFrom", "CountryCode", "IsSelected", "DealerType", "AdjustBasedOn", "pyFlowName", "pxPortal", "SearchType") Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[java] (INFO): Encountered exception during processing!
[java] Error converting data type varchar to numeric.
[java]
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) [java]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)[java]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
[java]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
[java]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
[java]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:17
[java]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
[java]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
[java]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.loadDatabaseTable(PegaBulkMover.java:1200)
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.process(PegaBulkMover.java:706)
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.main(PegaBulkMover.java:343)
[java]
[java] PegaBulkMover:: SQLException loading Database.
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.loadDatabaseTable(PegaBulkMover.java:1217)
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.process(PegaBulkMover.java:706)
[java]
at com.pega.pegarules.util.deploy.PegaBulkMover.main(PegaBulkMover.java:343)
Steps to Reproduce
Run the migrate.sh script.
Root Cause
A defect or configuration issue in the operating environment.
The source table has rows with a value of '0.000000000' in a decimal column. The Migrate script attempts to convert this to Numeric for the Insert statement and fails.
Resolution
Make the following change to the operating environment:
- Remove the rows with this value.
- Run the Migrate script.
Published February 10, 2020 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.