I am sure this kind of problem was delivery a thousand of time but I have little probleme about this.
I have a adapter connection with LOCAL_TRANSACTION as Transaction Type and I want work with an explicit transaction.
I have this STORED PROCEDURE in SQL (SQL Server with a Managed Service Account as credential with Windows Authentification).
ALTER PROCEDURE [dbo].[proc1] @isOpen bit AS DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT DECLARE @ErrorState INT BEGIN TRY -- I Let the user choose which kind of transaction he want to manage IF @isOpen = 0 BEGIN BEGIN TRANSACTION END INSERT INTO dbo.TableTest(TestField) VALUES ('Test') IF @isOpen = 0 BEGIN COMMIT TRANSACTION END return 0 END TRY BEGIN CATCH SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() ; IF @isOpen = 0 BEGIN ROLLBACK TRANSACTION END; THROW @ErrorMessage, @ErrorSeverity, @ErrorState END CATCH
I created this king of code
- pub.art.transaction:startTransaction (transactioName = test)
- SEQUENCE (Exit On SUCESS) Try/Catch
— SEQUENCE (Exit on FAILURE) Try
----- myStoredProcedureAdapter (ProcedureName = proc1, return value : Type = VARCHAR, Name = ReturnValue, parameter Input : Type = IsOpen, Type = VARCHAR). (isOpen = 1)
----- pub.art.transaction:commitTransaction transactioName = test)
— SEQUENCE (Exit On DONE) Catch
----- pub.art.transaction:rollbackTransaction (transactioName = test)
That seems correct to me but I noticed two problems :
- In debug mode, just after the step for stored procedure, when I check database, my record is filled (I expected to be locked in the table when I make a selection in SQL)
- After the commit step, I go to the Catch SEQUENCE with the error saying : Unable to commit a transaction null.
- Is it possible to manage explicit transaction with stored procedures ?
- If yes what is wrong with my code ?
- Is it a way that Managed Service Account and Windows Authentification as credentials could be a problem. (I don’t expect that, I use the WmDB package for the same issue, I have other problems but explicit transaction seems to work.
wM 9.10 for me.
Thanks for your answer.
PS : I am surprised that in startTransaction step, we don’t give the name of the Adapter Connection.