Upgrade to 15.x fails on NK_SUM_JULIAN_FCT with error "Incorrect syntax near the keyword 'with' on SQL Server

Products

• Clarity PPM On Premise

Issue/Introduction

When upgrading an MSSQL (SQL SERVER) environment to 15.x.x, an error is thrown:

12/23/19 11:24 AM (ExecTask) Process - function: NK_SUM_JULIAN_FCT.xml
12/23/19 11:24 AM (ExecTask) 
12/23/19 11:24 AM (ExecTask) SQL Text:
12/23/19 11:24 AM (ExecTask) 
12/23/19 11:24 AM (ExecTask) CREATE FUNCTION NK_SUM_JULIAN_FCT (@p_curve NVARCHAR(MAX), @p_start integer = 2, @p_finish integer = 65534)
12/23/19 11:24 AM (ExecTask)    RETURNS FLOAT
12/23/19 11:24 AM (ExecTask) BEGIN
[…]
Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

or

1/12/21 12:43 PM (ExecTask) Process - function: NK_SEGMENTS_JULIAN_FCT.xml
[...]
1/12/21 12:43 PM (ExecTask) java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
1/12/21 12:43 PM (ExecTask)

Environment

Release : All supported Clarity releases

Database: MSSQL 

Cause

This is caused by a wrong Compatibility level on the MSSQL database. 

Resolution

Request your DBA to set the Compatibility level accordingly for your version on the Niku (Clarity) database. For MSSQL 2016, compatibility has to be set to 130. For MSSQL 2017, the compatibility has to be set to 140.

This can be done either from MS SQL Management Studio or by using the queries below:

Here are some examples (replace with the actual database names) :

USE NIKU;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'NIKU'
GO  

use NIKU
ALTER DATABASE NIKU SET COMPATIBILITY_LEVEL = 130;
GO  

After the above is completed, retry the upgrade.