Time Slicing Job Failing after Upgrade

book

Article ID: 184121

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

After the upgrade from 15.7.0.1 to 15.7.1.1, the Time Slicing job is failing with the below error in the bg-ca log:

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

The database being used in this environment is Microsoft SQL Server 2016.

Additionally, projects and other investments cannot be created or updated. When an attempt to create or update one is made, the app-ca logs throw the same error as above.

Environment

Release : 15.7.1

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Resolution

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

Once the audit triggers such as the ones listed below are dropped or updated to no longer include this function, the Time Slicing job will complete and investments can be created/updated.

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 adding a new attribute to be audited on the object being referenced, saving this change and then removing this 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;