Custom Job failing after upgrading from clarity 14.3 to clarity 15.3

[Harun.Prasu]

Hi Experts,

 

I am having an issue with a custom job that is running through clarity after upgrading clarity from 14.3 to 15.3.

1.The  Job is a SQL Stored Procedure executable.

2.I am able to run the stored procedure successfully from studio.

 

While running the job through clarity I am getting the following error.

 

ERROR 2018-05-02 07:51:26,455 [Dispatch CF Manage Timesheet Edit & Approval rights for PM's & RM's : bg@SV-CLRTY15-T1 (tenant=clarity)] niku.njs (clarity:AAA3158:18096025__E519D015-489A-4D2D-A1C3-A970013326C7:CF Manage Timesheet Edit & Approval rights for PM''s & RM''s) Error executing job: 6427487
com.niku.union.persistence.PersistenceException: extractResultSets may be in an infinite loop
 at com.niku.union.persistence.PersistenceController.extractResultSets(PersistenceController.java:1897)
 at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2820)
 at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:868)
 at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:768)
 at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:576)
 at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:306)
 at com.niku.njs.listeners.SPJob.scheduledEventFired(SPJob.java:141)
 at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:652)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at java.lang.Thread.run(Thread.java:745)

 

 

I tried debugging the issue and failed. Please shed some light to it.

 

Thanks,

Harun

[Suman Pramanik]

Are you able to any other stored procedure via job from PPM 

[Harun.Prasu]

Hi SumanPramanik  Yes I am able to run all the other jobs with Stored Procedures. The same job is working fine on clarity 14.3.

[Harun.Prasu]

I think the issue is happening only when using nested cursor.  

[suhail-sayed]

Hi Harun,

 

Are all the related users, rights and custom object attributes in place after the upgrade to make sure the process works fine?

 

Suhail

[Harun.Prasu]

suhail-sayed

Hi Suhail,

 

Yes we have verified all the user rights, custom object attributes after the upgrade. What made you say that? Can you please elaborate a scenario?

 

Thanks,

Harun

[Roland Parrotte]

Harun, you indicate "2.I am able to run the stored procedure successfully from studio."  PPM Studio will only define the 'job' of executable type "SQL Stored Procedure".  It is not running the stored procedure.  You would need to run from 'Home/Jobs' which is technically not 'studio'.

 

Have you run the stored procedure directly on the database?  Given that you are jumping from V14.3 to V15.3 which may also have required the database also to be upgraded to a later version, the issue may now be with the way the 'stored procedure' is now functioning in the database, and needs to be corrected and the database stored procedure needs to be corrected, prior to retesting again in Clarity.

[Legacy User]

Right, by "studio" do you mean SQL Server Management Studio?  If so, are you logging in as a DBA or with Windows creds, or are you logging in with the application logon?  Also, how are you calling this stored proc?  Is the loop within the SQL code, or the gel script calling the SQL?

[Harun.Prasu]

The loop is within the SQL code and am getting this error only when running through application.

[Harun.Prasu]

Sorry for the confusion . I meant to say SQL server Management Studio.

We cant replicate the issue when running the stored procedure from SQL Management studio. We were using nested cursors in the stored procedure.

Somehow we managed to fix it by giving SET NOCOUNT ON in the beginning of SP. I wonder how the same worked on 14.3 clarity environment .

"Error :com.niku.union.persistence.PersistenceException: extractResultSets may be in an infinite loop"