Support Article
Migrate script throws errors for varbinary and bit columns
SA-10262
Summary
User upgrades from Pega 6.2 SP2 single schema on MS SQL 2008 to Pega 7.1.7 (Single Schema MS SQL 2012). There are multiple frameworks installed in this environment.
User runs the migrate.sh script to migrate the rules tables to a new rules schema as part of the upgrade but it fails for tables which have varbinary and bit database column types.
Error Messages
[java] ERROR: The table HealthCodes_COGP_CodeSets was ignored because the following column(s) were not recognized: pzPVStream: varbinary
[java] ERROR: The table pca_rule_hc_pcs was ignored because the following column(s) were not recognized: pzPVStream: varbinary
[java] ERROR: The table pr_index_circumstance_def was ignored because the following column(s) were not recognized: MDPrecertFlag: bit, FlagMAMember: bit, RouteToPvcyWB: bit
[java] ERROR: The table pr_Index_HC_PCS_BenefitMapping was ignored because the following column(s) were not recognized: pzPVStream: varbinary
[java] Error generating ddl for mssql, dbo
[java] Generated DDL cannot be applied automatically due to parsing errors
Steps to Reproduce
Run the migrate.sh script.
Root Cause
The tool that generates the DDL does not recognize varbinary or bit database column types.
Resolution
This issue is resolved through the following Local-change:
1. Set pega.clone.generate.xml=true and the remaining properties to false and run migrate script.
2. Modify pegarules-source.xml to change bit columns to pega:integer and varbinary to pega:blob.
3. Set pega.clone.create.ddl=true and remaining properties to false and run migrate script.
4. For each of the columns that were modified in the xml file, the [build distribution]/schema/generated/mssql/clone/mssql.sql file needs to be modified to create the correct column.
For Bit Column: Change INT column types to BIT (testBit INT NULL to testBit BIT NULL).
For Varbinary Column: Change IMAGE column types to VARBINARY(MAX) (testVarbinary IMAGE NULL testVarbinary VARBINARY(MAX) NULL).
5. Have DBA apply updated DDL file and then continue with the upgrade of the rules schema.
6. For the execution of the migrate script to generate the rules schema objects after the rules upgrade, if one of the problem tables was pr_index_circumstance_def, modify the generated mssql.sql file to remove the statements which will create this table before having the DBA apply the same.
Published June 12, 2015 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.