Install error: Argument data type varbinary(max) is invalid

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We tried to apply patch 1 for 15.9.1 (15.9.1.1) and it fails with the following error in the install.logs

[CA Clarity][SQLServer JDBC Driver][SQLServer]Argument data type varbinary(max) is invalid for argument 1 of textptr function.

Environment

Release : 15.9.1

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Database: MSSQL Server

Cause

This can be due to audit triggers in the database using the textptr function. 

Resolution

Drop and update the audit triggers such as the ones listed below:

• T_REQRESOURCE_RM_REQ_RESOURCES
• T_KEYMETRIC_DDPT_KEYMETRICS
• T_SERVICE_FFIN_FINANCIALS
• T_OTHER_FFIN_FINANCIALS
• T_APPLICATION_FFIN_FINANCIALS

The audit triggers can be updated by the steps below:

1. Add a new attribute to be audited on the object being referenced
2. Save this change
3. Remove the new attribute from the list of attributes to be audited.

To find if the textptr function is being used anywhere else, the following query can be used: 

select distinct
db_obj.name AS [Object Name], 
case when db_obj.xtype = 'P' then 'Stored Proc'
when db_obj.xtype = 'TF' then 'Function'
when db_obj.xtype = 'TR' then 'Trigger'
when db_obj.xtype = 'v' then 'View'
end as [Object Type], 
parent_obj.name
from sysobjects db_obj
inner join syscomments comment ON db_obj.id = comment.id
left join sysobjects parent_obj ON parent_obj.id = db_obj.parent_obj
where db_obj.type in ('P','TF','TR', 'V')
and db_obj.category = 0
and charindex ('textptr' ,comment.text)>0
order by 3,1;