[CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:17800000018

[Karim DIOUF]

Hello Experts,

We have a issue when we run a custom gel process that creates a project or a task from Demand.
[CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:17800000018.

Below you will find an extract of the error.

Exception Trace: [BDD]org.apache.commons.jelly.JellyTagException: null:143:81: <sql:query> SELECT next_value
FROM niku.CMN_AUTONUM_SCHEMES
WHERE 1=1
AND object_code = 'project'
AND attribute_code = 'unique_code'
AND partition_code = 'NIKU.ROOT': [CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:17800000018.
End Error Information


FATAL 2020-10-27 11:14:13,738 [Custom script execution pool-8-thread-2] union.persistence (clarity:admin:32363038__DFBC754F-6D7E-4B30-968D-C3C75AAFE400:none)
java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:17800000018.

Regards,
Karim

[Georgy]

Hi @Karim DIOUF​  

We are also facing a similar issue in the GEL processes. The same custom GEL script worked with no error/issue when the MS SQL version was SQL SERVER 2014 and  CLARITY version was v15.4.1.

Did the upgrade to v15.8.1 very recently along with upgrading the SQL version to SQL SERVER 2016. Seeing this error ever since then on those GEL scripts which make use of the CMN_AUTONUM_GET_NEXT_SP  to generate the next_value for an Object  and then extracting that new next value in the same GEL script using the "SELECT next_value FROM niku.CMN_AUTONUM_SCHEMES WHERE ....." statement.

org.apache.commons.jelly.JellyTagException: null:7:77:
<sql:query> SELECT next_value
		FROM niku.CMN_AUTONUM_SCHEMES
		WHERE
		object_code = 'costplan'
		AND attribute_code = 'code'
		AND partition_code = 'NIKU.ROOT':
[CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:4c000000ac.
	at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:194)
	at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248)
	at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:96)
	at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:187)
	at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20)
	at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248)
	at com.niku.union.gel.GELScript.run(GELScript.java:58)
	at com.niku.union.gel.GELController.invoke(GELController.java:74)
	at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:4c000000ac.
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.v(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.c(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.dda4.m(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.e(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.x(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.w(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.executeQuery(Unknown Source)
	at jdk.internal.reflect.GeneratedMethodAccessor119.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy34.executeQuery(Unknown Source)
	at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:175)
	... 11 more
Root cause
java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:4c000000ac.
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.v(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.tds.ddr.c(Unknown Source)
	at com.ca.clarity.jdbc.sqlserver.dda4.m(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.e(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.a(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.x(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.w(Unknown Source)
	at com.ca.clarity.jdbc.sqlserverbase.ddev.executeQuery(Unknown Source)
	at jdk.internal.reflect.GeneratedMethodAccessor119.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.tomcat...

Regards,
Georgy

[Karim DIOUF]

Hi Georgy,

Thank you for your message.
We did a recent upgrade from 15.4 to 15.7 with SQL Server 2014. All our custom processes are not working any more and failed with that error.
Do you still facing the error in your gel script ?

Regards,
Karim

[Georgy]

Hi Karim,

Yes.

We still have this issue but not in ALL custom GEL processes --- Only in those where we use the CMN_AUTONUM_GET_NEXT_SP  along with that  SELECT next_value FROM niku.CMN_AUTONUM_SCHEMES  query line. 

Regards,
Georgy

[Suman Pramanik]

Hi Georgy

The error of SQL server means thee transaction couldn't commit, we use Progress JDBC Driver in the application and what I see is you are executing a procedure CMN_AUTONUM_GET_NEXT_SP to get the next value and it could even clarity as application is using the same at the same time and you are running into this situation. 

In MSSQL Server table sequences are stored in a one row table with a prefix of CMN_SEQ. Below are examples of checking the sequence for common tables.  This is not inclusive of all table sequences.

SELECT * FROM CMN_SEQ_PRTASK;
SELECT MAX(PRID) FROM PRTASK;

SELECT * FROM CMN_SEQ_PRTEAM ; 
SELECT MAX(PRID) FROM PRTEAM ; 

SELECT * FROM CMN_SEQ_PRTIMEENTRY ; 
SELECT MAX(PRID) FROM PRTIMEENTRY ; 

So it will be best if you can alter your script and read sequence wuing the CMN_SEQ*  tables 

Hope it helps

------------------------------
Thanks & Regards
Suman Pramanik
Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
------------------------------