Support Article
Data upgrade case sensitivity error"Column names must be unique"
SA-53590
Summary
When running a data schema upgrade on SQL Server 2008 R2, the scripts display an error relating to column names not being unique.
The database displays an error when the application creates a column which already exists.
For example, PYDEPENDENCYANCESTORCLASS column exists in the table in uppercase and the script cannot create it with the pyDependencyAncestorClass name.
Error Messages
[java] (SQLLoaderInternal) INFO - Failed to execute SQL in file mssql05550.sql
[java]
[java] com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each table must be unique. Column name 'pyDependencyAncestorClass' in table 'dbo.pr_index_assignmentdeps' is specified more than once.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
[java] at
.
.
.
[java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:151)
[java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:110)
[java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:98)
[java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:77)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:65)
[java] ( SQLLoaderInternal) WARNING - Statement: ALTER TABLE dbo.pr_index_assignmentdeps ADD "pyDependencyAncestorClass" VARCHAR(96) NULL
[java] ( SQLLoaderInternal) WARNING -
[java] ------------------------------------------------------
[java]
[java] Encountered a SQL Exception, clean schema before running again.
[java]
[java] --------------------------------------------------
[java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements
[java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:80)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:65)
[java] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each table must be unique. Column name 'pyDependencyAncestorClass' in table 'dbo.pr_index_assignmentdeps' is specified more than once.
[java] at
.
.
.
com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:151)
[java] at
BUILD FAILED
D:\Pega_installables\115631_Pega7.31\scripts\setupDatabase.xml:2284: The following error occurred while executing this line:
D:\Pega_installables\115631_Pega7.31\scripts\setupDatabase.xml:2302: Java returned: 1
Total time: 11 minutes 28 seconds
(UpgradeDataModelImpl) INFO - Installed Product Versions:
(UpgradeDataModelImpl) INFO - {Instance: PEGARULES PROCESS COMMANDER!7.3.1, version: 06-03-15, build name: PRPC-7.3.1-184} (current version)
Exiting with Error
Steps to Reproduce
Run the script using the command upgrade.bat --dataOnly true.
Root Cause
A defect or configuration issue in the operating environment.
SQLServer 2008 is case sensitive and a previous Pega Installation script created some columns in uppercase.
When the SQL server instance was setup for the original system, the case sensitivity was enabled and the column is created in uppercase.
There are known bugs in the Installation scripts for the Pega Customer Service for Healthcare Framework product. The collation must be set as case insensitive when installing certain framework products.
Resolution
Here’s the explanation for the reported behavior:
The MS SQL Install script that is part of an earlier Pega Customer Service for Healthcare versions, required manual steps to name the column correctly after collation is set to case sensitive.
As a local-change, perform either of the following:
- Alter the column name to use the required mixed case name.
- Switch the collation as case insensitive in MS SQL database.
Published October 6, 2018 - 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.