When AutoCommit is set to false, executing the ASE stored procedure results in the following error message:
"ERROR [ZZZZZ] [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Stored procedure 'dbo.xsp_demo_rc_bug3670' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
This issue occurs only in the PowerServer installable cloud application when the .NET ODBC data provider is used, but not in the PowerBuilder C/S application.
Cause:
The PowerServer installable cloud application connects to ASE through the ODBC driver, while the PowerBuilder C/S app connects to ASE through the native driver. The error is due to differences in transaction mode handling between these two drivers. The default transaction mode of the ODBC driver does not match the transaction mode required by the stored procedure.
Solution:
1. Set SQLCA.Autocommit = true.
2. Before calling the stored procedure, execute the following SQL command to set the current session’s transaction mode to unchained.
execute immediate "SET CHAINED OFF";
3. Change the stored procedure’s mode to "anymode" by using the following command:
sp_procxmode 'xsp_demo_rc', 'anymode'
This allows the stored procedure to run in any transaction mode, accommodating different transaction settings.
