Upgrade fails with Incorrect syntax on GENERATE_CMN_SEQ_TABLES.xml

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Upgrade on MSSQL (SQL Server) fails with error:

 

2/25/21 3:09 PM (ExecTask) Command: install database schema file
2/25/21 3:09 PM (ExecTask) To URL: jdbc:clarity:sqlserver://test:1433;DatabaseName=niku;InsensitiveResultSetBufferSize=0;ProgramName=niku;DateTimeInputParameterType=dateTime
2/25/21 3:09 PM (ExecTask) As user: niku
2/25/21 3:09 PM (ExecTask) For vendor: mssql
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask) Process: D:\clarity\database\sql\GENERATE_CMN_SEQ_TABLES.xml
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask) SQL Text:
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask)   BEGIN
2/25/21 3:09 PM (ExecTask) java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'TESTTEST$'.
2/25/21 3:09 PM (ExecTask)     exec CMN_CREATE_SEQ_TABLES_SP;
2/25/21 3:09 PM (ExecTask)   END;
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask) 
2/25/21 3:09 PM (ExecTask) Total time: 0H:0M:0S

Environment

Release : Any

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Resolution

TESTTEST$ is not an OOTB table. So it gets it as result when it queries the SYSOBJECTS and tries to create a SEQUENCE table on it.

Look into the database and remove all custom tables, then retry the upgrade. 

 

Note: To make sure this works, you could run this after you remove the tables on your failed database before rolling back (for a test):

 BEGIN
    exec CMN_CREATE_SEQ_TABLES_SP;
  END;

If that goes through, feel free to rollback files and database and start the upgrade again.