January 5, 2018 at 2:40 am
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
January 5, 2018 at 8:50 am
andyc209 - Friday, January 5, 2018 2:40 AMThe 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_TABLEDECLARE @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 = ''
ENDSELECT * FROM TBL_TEST_TABLE
You would want to build the entire string with the @SQL variable instead of executing each part of the @SQL in pieces.
Just in terms of transaction in general and not related to the error, you generally want things outside of a explicit transaction if it's not part of the actual transaction - such as having the check for the SQL Server versions. You generally do those things before the transaction. You can get the SQL Server version value before the begin transaction.
And the select * from the table in a lot of places doesn't make much sense - maybe that was just for testing? You don't need those in the transaction though.
You can also print the statement you build for testing - just use print @sql instead of executing so you can verify the statement.
UNTESTED - If I made those changes on your script, it would look something like: DECLARE @SQL NVARCHAR (1000)
DECLARE @SQLVERSION NVARCHAR(128)
SET @SQLVERSION = CAST(SERVERPROPERTY('PRODUCTVERSION') AS NVARCHAR)
SET @SQLVERSION = SUBSTRING(@SQLVERSION, 1, CHARINDEX('.', @SQLVERSION) - 1)
SET @SQL = 'BEGIN TRANSACTION'
SET @SQL = @SQL +
' INSERT INTO TBL_TEST_TABLE (TEST_COLUMN)
VALUES (2020)'
IF @SQLVERSION <12
BEGIN
SET @SQL = @SQL + ' ROLLBACK'
--EXEC sp_executesql @SQL
PRINT @SQL
END
IF @SQLVERSION >10
BEGIN
SET @SQL = @SQL + ' COMMIT WITH (DELAYED_DURABILITY = ON)'
--EXEC sp_executesql @SQL
PRINT @SQL
END
SELECT * FROM TBL_TEST_TABLE
You can comment out the print statements and remove the comment from the exec statementsto run it. You can execute the sql printed from the print statements to verify whatever string you build is what you intended to have run.
Sue
January 5, 2018 at 9:23 am
Sue_H - Friday, January 5, 2018 8:50 AMandyc209 - Friday, January 5, 2018 2:40 AMThe 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_TABLEDECLARE @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 = ''
ENDSELECT * FROM TBL_TEST_TABLE
You would want to build the entire string with the @SQL variable instead of executing each part of the @SQL in pieces.
Just in terms of transaction in general and not related to the error, you generally want things outside of a explicit transaction if it's not part of the actual transaction - such as having the check for the SQL Server versions. You generally do those things before the transaction. You can get the SQL Server version value before the begin transaction.
And the select * from the table in a lot of places doesn't make much sense - maybe that was just for testing? You don't need those in the transaction though.
You can also print the statement you build for testing - just use print @sql instead of executing so you can verify the statement.UNTESTED - If I made those changes on your script, it would look something like:
DECLARE @SQL NVARCHAR (1000)
DECLARE @SQLVERSION NVARCHAR(128)SET @SQLVERSION = CAST(SERVERPROPERTY('PRODUCTVERSION') AS NVARCHAR)
SET @SQLVERSION = SUBSTRING(@SQLVERSION, 1, CHARINDEX('.', @SQLVERSION) - 1)SET @SQL = 'BEGIN TRANSACTION'
SET @SQL = @SQL +
' INSERT INTO TBL_TEST_TABLE (TEST_COLUMN)
VALUES (2020)'
IF @SQLVERSION <12
BEGIN
SET @SQL = @SQL + ' ROLLBACK'
--EXEC sp_executesql @SQL
PRINT @SQL
END
IF @SQLVERSION >10
BEGIN
SET @SQL = @SQL + ' COMMIT WITH (DELAYED_DURABILITY = ON)'
--EXEC sp_executesql @SQL
PRINT @SQL
ENDSELECT * FROM TBL_TEST_TABLE
You can comment out the print statements and remove the comment from the exec statementsto run it. You can execute the sql printed from the print statements to verify whatever string you build is what you intended to have run.
Sue
makes sense - was hoping not to make the inset part Dynamic because in truth this was only a test file and the real version has loads of code inside the transaction
will try and see what happens
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply