Last updated on JUNE 16, 2017

Applies to:

Symptoms

9.2 version, HR Maintenance

Change Assistant when Applying Change Package 022, PeopleTools 8.55.15, DMS with %substring fail MSSQL  

New installation of PeopleSoft System Database from CD release 9.2 , 016, Peopletools 8.55.14.
When we trying to apply the Change Package Created from PUM Image 22 using Change Assistant, all DMS Scripts from any step that Contains %substring will Always Fail.

Example Applying UPD25310470_01_DMS

Original Script is :
SET LOG upd25310470.log;
update PS_GP_MPSLP_STGED set GP_MPSLP_SPRNT_ORD = %substring(GP_MPSLP_SPRNT_ORD,1,3) where GP_PSLP_SRCPRODUCT like '%GPCHE%' ;

Change Assistant Modified Script :
SET LOG D:\Output\softwareupdateCHANGE_PACKAGEA{HR92PUM2-HR92DEV1}IP\upd25310470.log;
update PS_GP_MPSLP_STGED set GP_MPSLP_SPRNT_ORD = %substring(GP_MPSLP_SPRNT_ORD,1,3) where GP_PSLP_SRCPRODUCT like '%GPCHE%' ;

ERROR
-----------------------

This will fail in MSSQL all the time.

Started:  Thu Jun 15 15:31:12 2017
Data Mover Release: 8.55.14
Database: HR92DEV1 (ENG)
SQL Error. Error Position: 0  Return: 8601 - [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Argument data type smallint is invalid for argument 1 of substring function.
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180
UPDATE PS_GP_MPSLP_STGED set GP_MPSLP_SPRNT_ORD = SUBSTRING(GP_MPSLP_SPRNT_ORD,1,3) where GP_PSLP_SRCPRODUCT like '%GPCHE%'
Error: SQL execute error for UPDATE PS_GP_MPSLP_STGED set GP_MPSLP_SPRNT_ORD = %substring(GP_MPSLP_SPRNT_ORD,1,3) where GP_PSLP_SRCPRODUCT like '%GPCHE%'
Ended: Thu Jun 15 15:31:12 2017
Unsuccessful completion



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Run DMS scripts upd25081696 and upd25310470 as part of the update as an example

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Unable to proceed with the application of PUM 22 Using CA

Cause