tSQLt on SQL Server 2017 AG with DTC enabled

Question

I receive the following error when I am running tSQLt tests on SQL Server 2017 that is setup in an Always On availability group that is configured with DTC_SUPPORT = PER_DB on the AG group. If I turn off the DTC Support, it works fine. Our environment requires DTC. Is there a way to work around this in tSQLt or is there a different way to configure SQL so that both DTC and tSQLt will work?

Here is the error:

Test Procedure: [xxx].[zzfnCipValidateCustomerAddress].[test '12345' Zip Codes] on DbServer
[zzfnCipValidateCustomerAddress].[test '12345' Zip Codes] failed: (Error) Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.[16,1]{zzfnCipValidateCustomerAddress.test '12345' Zip Codes,36} (There was also a ROLLBACK ERROR --> The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.{tSQLt.Private_RunTest,160})

Here is one of the failing tests:

CREATE PROCEDURE [zzfnCipValidateCustomerAddress].[test '12345' Zip Codes]
AS
BEGIN
--Assemble
/*
Test to determine if function is an Inline Table Valued function or not
since this function is being rewritten to be an ITVF function and this test should
pass both versions
*/
DECLARE @IsITVF BIT;

SELECT @IsITVF = IIF(fc.FunctionCount > 0, 1, 0)
FROM
(
    SELECT COUNT(*) AS FunctionCount
    FROM sys.sql_modules AS sm
        JOIN sys.objects AS o
            ON sm.object_id = o.object_id
    WHERE sm.object_id = OBJECT_ID('dbo.fnCipValidateCustomerAddress')
          AND o.type = 'IF' --'IF' = Inline Valued Table Function
) AS fc;

SELECT @IsITVF;


--DROP TABLE IF EXISTS zzfnCipValidateCustomerAddress.TestData;

CREATE TABLE zzfnCipValidateCustomerAddress.TestData
(
    AddressLine1 VARCHAR(100),
    AddressLine2 VARCHAR(100),
    City VARCHAR(50),
    StateAbbr VARCHAR(3),
    ZipCode VARCHAR(9)
);

INSERT INTO zzfnCipValidateCustomerAddress.TestData
(
    AddressLine1,
    AddressLine2,
    City,
    StateAbbr,
    ZipCode
)
VALUES
('Test', NULL, 'Test', 'AZ', '12345');

CREATE TABLE zzfnCipValidateCustomerAddress.Expected
(
    AddressLine1 VARCHAR(100),
    AddressLine2 VARCHAR(100),
    City VARCHAR(50),
    StateAbbr VARCHAR(3),
    ZipCode VARCHAR(9),
    CipExceptionReasonId INT
);

INSERT INTO zzfnCipValidateCustomerAddress.Expected
(
    AddressLine1,
    AddressLine2,
    City,
    StateAbbr,
    ZipCode,
    CipExceptionReasonId
)
VALUES
('Test', NULL, 'Test', 'AZ', '12345', 8);

--Act

IF (@IsITVF = 0)
BEGIN
    SELECT td.AddressLine1,
           td.AddressLine2,
           td.City,
           td.StateAbbr,
           td.ZipCode,
           dbo.fnCipValidateCustomerAddress(td.AddressLine1, td.AddressLine2, td.City, td.StateAbbr, td.ZipCode) AS CipExceptionReasonId
    INTO zzfnCipValidateCustomerAddress.Actual
    FROM zzfnCipValidateCustomerAddress.TestData AS td;
END;
ELSE
BEGIN
    SELECT fcvcat.AddressLine1,
           fcvcat.AddressLine2,
           fcvcat.City,
           fcvcat.StateAbbr,
           fcvcat.ZipCode,
           fcvcat.CipExceptionReasonId
    INTO zzfnCipValidateCustomerAddress.Actual
    FROM zzfnCipValidateCustomerAddress.TestData AS td
        CROSS APPLY dbo.fnCipValidateCustomerAddress(
                                                        td.AddressLine1,
                                                        td.AddressLine2,
                                                        td.City,
                                                        td.StateAbbr,
                                                        td.ZipCode
                                                    ) AS fcvcat;
END;


--Assert

EXEC tSQLt.AssertEqualsTable @Expected = N'zzfnCipValidateCustomerAddress.Expected',
                             @Actual = N'zzfnCipValidateCustomerAddress.Actual',
                             @Message = N'',
                             @FailMsg = N'Zip with value of ''12345'' did not generate a CipExceptionReasonId as expected';

END;

Answer 1

tSQLt is internally relying on transaction savepoints. Savepoints are not compatible with distributed transactions.

There is currently no way in tSQLt to change the handling of transactions.

My recommendation is to set up a dedicated CI (continuous integration) environment that is dedicated to executing your automated test suite (and therefore would not need AlwaysOn enabled). Independent of your specific situation, that is an industry best practice anyway.

However, that does not solve testing procedures that deal with your AlwaysOn setup specifically. Those you naturally have to test in an environment with AlwaysOn enabled. In that case, you could make use of the tSQLt.NewConnection command which executes the command passed in on a separate connection and with that outside of the tSQLt transaction.

Keep in mind, though, that you'd be responsible for all cleanup actions in that case, as anything executed through tSQLt.NewConnection clearly does not get rolled back by tSQLt.