Tag Archives: Query Editor

Subquery returned more than 1 value

Posted by on January 13, 2009 No comments

Problem:

File: e:pt84915b-retailpeopletoolssrcpsappengaedebug.hSQL error. Stmt #: 16
 03 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]
 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or 
when the subquery is used as an expression.
 [Microsoft][SQL Native Client][SQL Server]The statement has
 Failed SQL stmt:UPDATE PS_PTPP_OPTIONS SET PTPP_REG_PREFIX = 
( SELECT A1.EOPP_REG_PREFIX FROM PS_EOPP_OPTIONS A1)
 Message Set Number: 200
 Message Number: 0
 Message Reason: File: e:pt84915b-retailpeopletoolssrcpsappengaedebug.hSQL
 error. Stmt #: 1603 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]Subquery 
returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or 
when the subquery is used as an expression.
 [Microsoft][SQL Native Client][SQL Server]The statement has
 Failed SQL stmt:UPDATE PS_PTPP_OPTIONS SET PTPP_REG_PREFIX = 
( SELECT A1.EOPP_REG_PREFIX FROM PS_EOPP_OPTIONS A1) (200,0)
Process 7113 ABENDED at Step UPGPT846PP.EO_TO_PT.Step51a (SQL) -- RC = 8601 (108,524)
 Message Set Number: 108
 Message Number: 524
 Message Reason: Process 7113 ABENDED at Step UPGPT846PP.EO_TO_PT.Step51a (SQL)
 -- RC = 8601 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

Solution:

Ran:

SELECT * FROM PS_EOPP_OPTIONS A1
Two rows returned. Ran:

SELECT DISTINCT * FROM PS_EOPP_OPTIONS A1
One row returned.

Checked PS_EOPP_OPTIONS table in FSDEMO. It has one row.

Solution: Delete duplicate row:

SET ROWCOUNT 1
DELETE FROM [FSDMO].[dbo].[PS_EOPP_OPTIONS]

All Processing Suspended: Restart OPRID=VP1

Posted by on January 11, 2009 No comments

Problem:

When running the UPGPT846PP Application Engine program the following error appears:

C:>E:PSOFTFS900binclientwinx86psae -CD FSDMO -CT MICROSFT -CO VP1 -CP VP1 -R INSTALL -AI UPGPT846PP
PeopleTools 8.49.15 – Application Engine
Copyright (c) 1988-2008 PeopleSoft, Inc.
All Rights Reserved

All Processing Suspended: Restart OPRID=VP1, RUNID=INSTALL, PI=7078 (108,503)
Message Set Number: 108
Message Number: 503
Message Reason: All Processing Suspended: Restart OPRID=VP1, RUNID=INSTALL, PI=7078 (108,503)

C:>

Solution:

When an application engine program is restarted, it returns:

All Processing Suspended: Restart OPRID=%1 RUNID=%2, PI=%3

Delete the offending application engine run control record using the following SQL statement:

delete from PS_AERUNCONTROL where OPRID = %OPRID% and PROCESS_INSTANCE = %PI%

That is, in SQL Server Query Editor, choose FSDMO and run:

select * from PS_AERUNCONTROL

Process Instance OPRID RUN_CTRL_ID AE_APPLID RUN_DTTM
7078 VP1 INSTALL UPGPT846PP 40:39.0

delete from PS_AERUNCONTROL where OPRID = ‘VP1’ and PROCESS_INSTANCE = 7078

Reference:
http://www.zutshigroup.com/site/tech/app_engine_processing_suspended