Identity Governance post 14.3 CP2 - import from jcs fails wth SQL error: String or binary data would be truncated.

Products

• CA Identity Suite

Issue/Introduction

ERROR [com.eurekify.businessflow.bb.multiimport.Enrichment] (taskExecutorServiceContainer-4) Fail in taskId:xxx cause: java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [if exists(select 1 from ResourceFields WHERE ResourceID=? and DatabaseID=? and FieldNumber=?) UPDATE ResourceFields set FieldValue =? WHERE ResourceID=? and DatabaseID=? and FieldNumber=? else INSERT INTO ResourceFields (ResourceID, DatabaseID, FieldNumber, FieldValue) values (?,?,?,?)]; String or binary data would be truncated.; nested exception is java.sql.BatchUpdateException: String or binary data would be truncated.: java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [if exists(select 1 from ResourceFields WHERE ResourceID=? and DatabaseID=? and FieldNumber=?) UPDATE ResourceFields set FieldValue =? WHERE ResourceID=? and DatabaseID=? and FieldNumber=? else INSERT INTO ResourceFields (ResourceID, DatabaseID, FieldNumber, FieldValue) values (?,?,?,?)]; String or binary data would be truncated.; nested exception is java.sql.BatchUpdateException: String or binary data would be truncated.

Caused by: java.sql.BatchUpdateException: String or binary data would be truncated.

 

Environment

Any version of Identity Governance after 14.3 CP2

Cause

In 14.3 CP2 schema was changed so: 

dbo.UserFields FieldValue

dbo.ResourceFields FieldValue

dbo.HashFields TruncatedField

dbo.ConfigurationRoles RoleDescription 

would fit 768 characters.

This might not be enough with some LDAP group names exceeding FieldValue symbol limit.

Resolution

Our suggestion was to change table dbo.ResourceFields - FieldValue to nvarchar(max). 

This allowed value to be written to the field and resolved customer's problem.

Ona caveat is that from now on customer will have to make sure after every schema update to change this field to same nvarchar(max).

Customer was advised to create enhancement request so change will be implemented product wide.

https://knowledge.broadcom.com/external/article?articleId=44090