http://search.sybase.com/kbx/solvedcases?id_number=41046890
Case Description
Symptom 1 — Receiving following error in debug log: “powersoft.powerj.db.java_sql.Query: [jf_pif.query_pif update query in getMoreResults] could not obtain more results due to exception: com.sybase.jdbc.SybSQLException: Stored procedure ‘sp_proc_name’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.”
Symptom 2 — Using a stored procedure as a datasource for a datawindow, receiving the following error: “Select error:Stored procedure ‘sp_proc_name’ may be run only in unchained transaction mode. The Set Chained Off command will cause the current session to use unchained transaction mode.”
Tip or Workaround
A stored procedure that executes against an Adaptive Server Enterprise (ASE) database can be set to run in one of three transaction modes: CHAINED, UNCHAINED, and ANY. The sql statement “set chained on/off” will set the chained mode on and off, respectively. The transaction mode ANY will run in both chained or unchained mode.
PowerJ uses jConnect (JDBC) to connect to ASE databases. The chained mode is tied to the AutoCommit() mode of the JDBC connection.
If the stored procedure is set to run in Unchained mode, then set AutoCommit to True, and if the stored procedure is set to run in Chained mode, AutoCommit should be set to False. If the stored procedure is set to run in ANY transaction mode, it doesn’t matter what the AutoCommit mode is.
The AutoCommit mode can be found on the property sheet of the PowerJ transaction object. To adjust the AutoCommit mode, double-click the transaction object in your PowerJ project. Click on the Options tab.
To set AutoCommit to True: Under Initial Settings, choose “Set the following properties” and check (enable) AutoCommit. Click on OK.
Once you have adjusted the AutoCommit mode, re-run the project.
{ASE ships a stored procedure called sp_proc_xmode. It takes two parameters: a stored procedure name, and a transaction mode. This stored procedure can be used to change the transaction mode of any of your existing stored procedures.}
Resolution
To rectify the ‘Set Chained Off’ error, do the following:
1. Check the transaction mode of the stored procedure
2. If the mode of the stored procedure is Unchained, set AutoCommit to True. If the transaction mode is Chained, set AutoCommit to False.
========================
basically, its
EXEC sp_procxmode ‘dbo.p_CheckHasCashOrder’,’unchained’
if autocommit is set true.
