Nested Transactions with Batch Operations

[Sebastian]

I'm using Batch Operations according to the documentation (https://www.devart.com/sdac/docs/batchops.htm), but there is a problem with using a Transaction around the Batch-Operation.

It seems like Execute(x, x) is trying to use an implicit_transaction, but doesn't expect an already open transaction.
Wenn using Execute it executes "set implicit_transactions on SET NO_BROWSETABLE OFF" before the actual CommandText and afterwards "IF @@TRANCOUNT > 0 COMMIT TRAN" gets executed, but when a transaction is already open "implicit_transactions on" doesn't start a new transaction, which results in getting the outer transaction committed.

Here is a simplified example of what happens:

Code: Select all

SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
WHILE (@@TRANCOUNT > 0) COMMIT TRANSACTION;
CREATE TABLE #t1 (a INT);
GO

PRINT N'[01] BEGIN TRANSACTION';
BEGIN TRANSACTION;
PRINT N'[01] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO 

PRINT N'[02] SET IMPLICIT_TRANSACTIONS ON (SDAC)';
SET IMPLICIT_TRANSACTIONS ON;
PRINT N'[02] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N'[03] INSERT INTO';
INSERT INTO #t1 VALUES (11), (12);
PRINT N'[03] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
go

PRINT N'[04] IF @@TRANCOUNT > 0 COMMIT TRAN (SDAC)';
IF @@TRANCOUNT > 0 COMMIT TRAN;
PRINT N'[04] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N'[05] COMMIT TRANSACTION';
COMMIT TRANSACTION;
PRINT N'[05] @@TRANCOUNT == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO 

DROP TABLE #t1
GO

Code: Select all

[01] BEGIN TRANSACTION
[01] @@TRANCOUNT == 1
[02] SET IMPLICIT_TRANSACTIONS ON (SDAC)
[02] @@TRANCOUNT == 1
[03] INSERT INTO
[03] @@TRANCOUNT == 1
[04] IF @@TRANCOUNT > 0 COMMIT TRAN (SDAC)
[04] @@TRANCOUNT == 0
[05] COMMIT TRANSACTION
Meldung 3902, Ebene 16, Status 1, Zeile 28
Die COMMIT TRANSACTION-Anforderung hat keine entsprechende BEGIN TRANSACTION-Anweisung.
[05] @@TRANCOUNT == 0

I looked everywhere but this behavior isn't documented.

[Stellar]

Unfortunately, we can't reproduce the issue. To investigate this behavior of SDAC, please compose a full sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

[Stellar]

Thank you for the information. Internal transactions were removed from batch operations in the latest version of SDAC.
Please check whether the issue occurs in the latest version 9.1.3 of SDAC and let us know the result.