I have a stored procedure which its transaction mode is set to ‘Unchained’ upon creation.
However, I can not execute this stored procedure via adapter service. Below is the error thrown by Sybase:-
[ADA.1.316] Cannot execute the SQL statement "exec po_vas_search_member ?, ?, ?, ?, ?, ?, ?
". "
(ZZZZZ/7713) Stored procedure ‘po_vas_search_member’ may be run only in unchained transaction mode. The ‘SET CHAINED OFF’ command will cause the current session to use unchained transaction mode.
I also tried to execute ‘SET CHAINED OFF’ in custom SQL template prior to invoking the stored procedure. However, I get error below:-
[ADA.1.316] Cannot execute the SQL statement “SET CHAINED OFF”. "
(ZZZZZ/226) SET CHAINED command not allowed within multi-statement transaction.
I am not suppose to change transaction mode of this stored procedure to “any mode”. May I know if there is any other way to invoke this service via adapter service?
I tried to set to unchained mode prior to executing my stored procedure. Here is how i do it:-
“if @@tranchained <> 0
BEGIN
SET CHAINED OFF
END
exec po_vas_search_member ?, ?, ?, ?, ?, ?, ?”
Sybase then returned following error in the first attempt:-
[ADA.1.316] Cannot execute the SQL statement “SET CHAINED OFF”. "
(ZZZZZ/226) SET CHAINED command not allowed within multi-statement transaction.
However, when I executed this fragment of code again, it worked fine and retuned expected result. Out of curiosity, I then executed this fragment of code again after 10 minutes. Guess what, Sybase returned the same error. Then I executed the service continuously after that, it worked fine. if I let it sat for another 10 minutes, the same error would pop up.