Benefits Snapshot Error: 'SUBSTR' Is Not A Recognized Built-In Function Name in SQL Server (Doc ID 1375594.1)

Last updated on MARCH 11, 2015

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

When attempting to run the Benefits Snapshot Process (ben100a sqr) in SQL Server environment, it fails with following error message:

ERROR
-----------------------
"(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 16954 in cursor 71:
Microsofthttp://SQL Server Native Client 10.0SQL ServerExecuting SQL directly; no cursor.
(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 195 in cursor 71:
Microsofthttp://SQL Server Native Client 10.0SQL Server'SUBSTR' is not a recognized built-in function name.
(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 8180 in cursor 71:
Microsofthttp://SQL Server Native Client 10.0SQL ServerStatement(s) could not be prepared.
Error on line 3174:
(SQR 3716) Error in SQL statement.
Errors were found in the program file.
SQR for PeopleSoft: Program Aborting."

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to: Benefits> Interface with Providers> Benefit Snapshot Process
2. Enter As of Date
3. Run the ben100a sqr process.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms