Saving all 2000 characters in sting attribute fails

Products

• Clarity PPM On Premise

Issue/Introduction

We have a problem with saving all 2000 characters in a custom attribute on mux (popis).

On TEST environment (SQL Server 2019 15.0.4198.2) it was reported, that Clarity saves only 850 characters and the following error massage was received: SYSTEM ERROR. CONTACT SYSTEM ADMINISTRATOR.

• Tried the issue reproduction as Admin and the result was the same.
• When the same is tested on DEV environment (SQL Server 2019 Developer edition) and all 2000 characters were saved.
• Attributes on both environments seems to be the same, created as Investment attribute - string (2000). 

 

Environment

Clarity 16.0.2

Cause

Custom index on the table.

Resolution

Application logs:

ERROR Date... [https-openssl-nio2-443-exec-8] niku.xql2 (clarity:admin:15671219__9FE3609C-07F4-4367-8312-EF76BE207B4A:PPM_REST_API) Internal Processing exception
com.niku.union.persistence.PersistenceException: 
SQL error code: 1946
Error message: Operation failed. The index entry of length 4000 bytes for the index 'NonClusteredIndex-20220125-125526' exceeds the maximum length of 1700 bytes for nonclustered indexes.
Executed.....

The custom index structure created on the object table causing a known MSSQL limitation on non-clustered indexes, the suggestion would be to drop it. If creating the index back then you could try the workaround of creating it with the included columns logic as per the forum below:

https://stackoverflow.com/questions/56936219/workaround-maximum-key-length-for-a-nonclustered-index-in-sql-server

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017