Publish Resource Role Assignment Spread Job on an MSSQL Database Fails

(Doc ID 2179687.1)

Last updated on JANUARY 30, 2017

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 16.1.2.0 and later
Primavera P6 Enterprise Project Portfolio Management Cloud Service - Version 16.1.2.0 and later
Information in this document applies to any platform.

Symptoms

When attempting to run The Publish Resource Role Assignment Spread job, the following error occurs:

ERROR

 In the /services/JT_*/ text logs :
<TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [9] POST(SQL): Task started[<TIMESTAMP>]
SEVERE: <TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): Errors occurred when executing following statement:

insert into rsrcroleasgnmentspread(
rsrc_id,spread_start_date,spread_end_date,
plan_qty,staffed_act_qty,unstaffed_act_qty,act_this_per_qty,total_period_qty,
staffed_remain_qty,unstaffed_remain_qty,staffed_total_qty,unstaffed_total_qty,
plan_cost,act_this_period_cost,total_period_cost,staffed_act_cost,unstaffed_act_cost,
staffed_remain_cost,unstaffed_remain_cost,staffed_total_cost,unstaffed_total_cost,rolledup_record)
select rsrc_id,spread_start_date,spread_end_date,pq,saq,uaq,atpq,tpq,srq,urq,stq,utq,pc,atpc,tpc,sac,uac,src,urc,stc,utc,'Y'
from
(select rsrc_id,spread_start_date,spread_end_date,
sum(plan_qty) pq,sum(staffed_act_qty) saq,sum(unstaffed_act_qty) uaq,sum(act_this_per_qty) atpq,sum(total_period_qty) tpq,
sum(staffed_remain_qty) srq,sum(unstaffed_remain_qty) urq,sum(staffed_total_qty) stq,sum(unstaffed_total_qty) utq,
sum(plan_cost) pc,sum(act_this_period_cost) atpc,sum(total_period_cost) tpc,sum(staffed_act_cost) sac,sum(unstaffed_act_cost) uac,
sum(staffed_remain_cost) src,sum(unstaffed_remain_cost) urc,sum(staffed_total_cost) stc,sum(unstaffed_total_cost) utc
from rsrcroleasgnmentspread
where rsrc_id is not null and rolledup_record ='N'
group by rsrc_id,spread_start_date,spread_end_date order by spread_start_date)
SEVERE: <TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)
at com.primavera.infr.db.PPreparedStatement.execute(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.executeTask(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.infr.threadpool.ThreadPoolImpl.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
<TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): Task completed[<TIMESTAMP>]
 

 On some occasions, the following error will appear in the WebAccessLog HTML file :

sep-30-2016 11:30:31 391, CEST [ACTIVE] ExecuteThread: '11' for queue: 'weblogic.kernel.Default (self-tuning)' ERROR /rest/admin/serviceStatus/load (User Id: 91 DB Id: 1) com.primavera.bo.rules.calc.JobServiceCE ?:? Error parsing job_data blob for job 'PX Resource Role Assignment Spread 4786'. It will be treated as empty list. Bad job data was: SL_Taskrsrc

com.primavera.PhoenixException: The input to the Decryptor was invalid.
at com.primavera.common.b.b(Unknown Source)
at com.primavera.common.b.a(Unknown Source)
at com.primavera.infr.blobutils.BlobPropertyList.<init>(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.parseJobData(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.calc_JobDataParsed(Unknown Source)
at com.primavera.bo.schema.dispatch.JobServiceCEDispatch.dispatch(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.dispatch(Unknown Source)
at com.primavera.bo.base.CalculateEngineImpl.getObjects(Unknown Source)
at com.primavera.bo.base.CalculateEngineImpl.getObjects(Unknown Source)

 

STEPS

The issue can be reproduced at will with the following steps:
1. Enable publication
2. Publish a project containing resource and/or role data
3. Check the publication logs
4. Notice the error

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