(MSSQL) Upgrade fails on NK_SUM_JULIAN_FCT with error "Incorrect syntax near the keyword 'with'

book

Article ID: 142274

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

When upgrading an MSSQL 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 with MSSQL 

Resolution

This is caused by a wrong Compatibility level on the MSSQL database. For MSSQL 2016, compatibility has to be set to 130. For MSSQL 2017, the compatibility has to be set to 140

Please request your DBA to set the Compatibility level accordingly for your version on the niku database. They can do that either from MS SQL Management Studio or use the queries below:

You have to run the queries to reset the database compatibility level. 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  

 

Then retry again.