My Oracle Support Banner

PSARCHIVE Process Abends at Step PSARCHIVE.SETBASED.Step003 with Error "8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'AND'." When Archive Template "AUDT_TLR" Is Used on Microsoft SQL Server Database (Doc ID 3007010.1)

Last updated on FEBRUARY 27, 2024

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

PSARCHIVE Application Engine process fails on Microsoft SQL Database when Archive Template "AUDT_TLR" is used with the following error:

Error Position: 0
Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'AND'.
[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 42000) 8180

Statement: DELETE FROM PS_AUDIT_TLRPTTIME WHERE EXISTS (SELECT 'X' FROM PS_AUDIT_TLRPT_HST WHERE PS_AUDIT_TLRPT_HST.PSARCH_ID = 'AUDT_TLR' AND PSARCH_BATCHNUM = 11 AND

Original Statement: DELETE FROM PS_AUDIT_TLRPTTIME WHERE EXISTS (SELECT 'X' FROM PS_AUDIT_TLRPT_HST WHERE PS_AUDIT_TLRPT_HST.PSARCH_ID = 'AUDT_TLR' AND PSARCH_BATCHNUM = 11 AND

SQL error in Exec. (2,280) PSARCHIVE.SETBASED.GBL.default.1900-01-01.Step003.OnExecute PCPC:247 Statement:6

Process 496325 ABENDED at Step PSARCHIVE.SETBASED.Step003 (PeopleCode) -- RC = 8 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s


Steps to replicate the issue:
1. Navigate to Peopletools > Data Archive Manager > Archive Data to History run control page.
2. Create a new Run Control Id with Select Archive Template "AUDT_TLR" and Process Type "Delete".
3. Run the Process PSARCHIVE.
4. Process fails with the above error.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.