Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

SQL Error apply DDL updating from 7.1.7 to 7.2

SA-22713

Summary



Updating from Pega 7.1.7 split schema SQL Server to Pega 7.2. Doing an in place update.


Error Messages



     [java] Throwable occurred: com.microsoft.sqlserver.jdbc.SQLServerException: Table 'pr_index_circumstance_def' already has a primary key defined on it.
     [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.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
     [java]     at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
     [java]     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
     [java]     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
     [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.SQLServerStatement.execute(SQLServerStatement.java:662)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266)
     [java] Mar 25, 2016 10:37:19 AM com.pega.pegarules.install.SQLLoader writeMessage
     [java] WARNING: Statement: ALTER TABLE rules.pr_index_circumstance_def
     [java] ADD CONSTRAINT pr_index_circumstance_def_PK PRIMARY KEY NONCLUSTERED 
     [java] (
     [java]     pzInsKey
     [java] )
     [java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException
 

[java] Throwable occurred: com.microsoft.sqlserver.jdbc.SQLServerException: 'pr_page_store_PK' is not a constraint. 
[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.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792) 
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689) 
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) 
[java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) 
[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.SQLServerStatement.execute(SQLServerStatement.java:662) 
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266) 
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage 
[java] WARNING: Statement: ALTER TABLE dbo.pr_page_store 
[java] DROP CONSTRAINT pr_page_store_PK 
[java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException

Steps to Reproduce



Run update script.


Root Cause



A defect in Pegasystems’ code or rules.  

There are two issues.  For the pr_index_circumstance_def table there is a bit column type which is not anticipated but the tool generating the upgrade DDL.  As a result the constraints or indexes are not detected as being already installed and the applying of the generated ddl fails when it tries to create them.  

There is a secondary issue where the generated ddl by the update script is being sorted so that all of the drops happen first.  This causes an issue where the pr_page_store_PK is trying to be dropped when it does not exist at this point.


Resolution



Perform the following local-change: 

1.   Obtain modified SQL file from GCS which removes the statements trying to add the existing constraints and index and rearrange the order.

2.  Ensure that the DBA applies the modified SQL.
3.  Continue with the update by setting bypass.pega.schema=true in setupDatabase.properties file and restarte the update script.
Suggest Edit

Published May 4, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

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

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us