Transaction Level Delayed Durability

Transact-SQL
1

Many of our client SQL servers are 2014 so can use Transaction Delayed Durability fine but we still have some clients using 2012 - problem is we deploy the same code to all clients.
In order for this to work on all servers i was looking at using Dynamic SQL to help parse the code over all servers - applied a version check and then Dynamic SQL
I am getting the following errors from the code :
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

The update works to the table fine though despite the error - have i done something wrong in there

DECLARE @SQL NVARCHAR (1000)

SELECT * FROM TBL_TEST_TABLE

SET @SQL = 'BEGIN TRANSACTION'
EXEC sp_executesql @SQL
SET @SQL = ''

			INSERT INTO TBL_TEST_TABLE (TEST_COLUMN)
			VALUES (2020)
			SELECT * FROM TBL_TEST_TABLE

DECLARE @SQLVERSION NVARCHAR(128)
SET @SQLVERSION = CAST(SERVERPROPERTY('PRODUCTVERSION') AS NVARCHAR)
SET @SQLVERSION = SUBSTRING(@SQLVERSION, 1, CHARINDEX('.', @SQLVERSION) - 1)
	
	IF @SQLVERSION < 12 
	BEGIN
		
		SET @SQL = 'ROLLBACK'
		EXEC sp_executesql @SQL
		SET @SQL = ''
	END
	

	IF @SQLVERSION > 10
	BEGIN
	 	
		SET @SQL = 'COMMIT WITH (DELAYED_DURABILITY = ON)'	
		EXEC sp_executesql @SQL
		SET @SQL = ''
	END 

SELECT * FROM TBL_TEST_TABLE
2

So if it's SQL Server 2012 or below, you ROLLBACK. Then, if it's 2012 or higher, you COMMIT. That means, on a SQL Server 2012 system, you try to do both. Or am I reading this incorrectly?

Also, I'm not crazy about the implied conversion of integers to NVARCHAR. Explicit is better than implicit.

3

Sorry - left that ROLLBACK in there - i need to commit for all servers - its just i need the COMMIT with (DELAYED_DURABILITY = ON) to parse in pre 2014 which it wont without using Dynamic SQL

  IF @SQLVERSION < 12 
BEGIN
	
	SET @SQL = 'COMMIT'
	EXEC sp_executesql @SQL
	SET @SQL = ''
END
4

OK -- but your big problem is sp_executesql runs in a different context. That's why you get the error you do. What you would need to do is execue the whole query:

Begin Tran;insert/update;commit

as a dynamic sql query

5

ah right - thanks - was hoping to avoid that because on the real SP there is more than just the insert inside the transaction - this was just a test.
will test it now