Mixing
tSQLt on SQL Server 2017 AG with DTC enabled
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;
sql-server alwayson tsqlt asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
|
show 1 more comment
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;
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32
|
show 1 more comment
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;
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
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;
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
edited Nov 20 '18 at 15:40
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
asked Nov 19 '18 at 15:21
silverbullettruck2001
2017
2017
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32
|
show 1 more comment
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377715%2ftsqlt-on-sql-server-2017-ag-with-dtc-enabled%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
add a comment |
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.
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
add a comment |
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.
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
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.
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
answered Nov 27 '18 at 17:08
Sebastian Meine
8,9851728
8,9851728
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377715%2ftsqlt-on-sql-server-2017-ag-with-dtc-enabled%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'd need a lot more information than this to even start troubleshooting. Starting with, what does the failing test look like. Is there one particular statement that fails? Does the test involve any form of transaction management? - Or is every test failing in that environment?
– Sebastian Meine
Nov 20 '18 at 14:30
I added in an example of a test that is failing as requested. I am not using any transaction management in the tests. Also, all tests are failing. The function being tested is a table valued function that also is a table valued function and does not involve any transaction management. If there is any other information you need please let me know.
– silverbullettruck2001
Nov 20 '18 at 15:46
So, what I don't understand is why you are in an environment with always-on enabled. Tests (and in particular tSQLt) should never be run in production. Can you tell me more about your setup?
– Sebastian Meine
Nov 20 '18 at 17:41
@sebastian-meine Our DBAs use our development environment as their "development" area as well, so they setup all functionality that they want to implement in production environment to our development environment first and then promote those features. This allows them to verify that their AlwaysOn configurations work in Dev first and they can verify that it won't break when they implement it in UAT and Production. They do the same thing for other features/configurations as well. The objective is to has as few differences between each of our environments.
– silverbullettruck2001
Nov 20 '18 at 19:18
If you create an empty test (one that contains just a RETURN statement), and then execute just that test, do you still get the same error?
– Sebastian Meine
Nov 21 '18 at 11:32