Hi,
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.flow:getLastError
  ----- pub.art.transaction:rollbackTransaction (transactioName = test)

That seems correct to me but I noticed two problems :

• 1. 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)
• 2. After the commit step, I go to the Catch SEQUENCE with the error saying : Unable to commit a transaction null.

So :

• 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.

OK thanks to this discussion :
[url]http://tech.forums.softwareag.com/techjforum/posts/list/47494.page[/url]

I find the problem : it seems that in debug mode, transaction doesn’t work.
I try to do call my proc two times (Violation on primary key in database).

If Transaction does"nt work I should have only one row inserted. If work, I should be no row inserted.
I try it in runtime and the result is I have no row in database.
It seems to work.

I understand why mechanism in debug mode deosn’t work. Probably to not locked the database. But I wonder if it is possible to force the explicit transaction anyway as someone knowing what he does.

Hi Vital,

this is a known issue for quite a long time now.

Services with explicit transaction handling cannot be debugged in Developer/Designer.

startTransaction does not require a connection name, as it can span over several different connections (even for different adapter types), i.e. when using XA-Transactions.
You only need to make sure that the transaction name is handled correctly by startTransaction and commitTransaction/rollbackTransaction.

Regards,
Holger