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