SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Few days ago, one of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them.

Example:
PRINT 'After ROLLBACK example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
ROLLBACK TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO
PRINT '--------------------'
PRINT 'After COMMIT example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
COMMIT TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO

ResultSet:
After ROLLBACK example
1
2
2
——————–
After COMMIT example
1
2
2

It is very clear that local variables are not affected with transaction. In both of the above cases we got the same result. Jr. DBA and Application Tech Lead were pleased with this simple example and asked me to blog about it. Let me know if you like this kind of articles. I will post some more like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error: 3902, Severity: 16; State: 1 : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

SQL Server Integration Services Error : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (Microsoft OLE DB Provider for SQL Server)

Fix/Workaround/Solution:

Option 1:
To work around this problem, do not call the stored procedure by using ODBC Call syntax. You can call the stored procedure in may ways by using ADO. One of the methods is to call a stored procedure by using a command object. (View Example)

Option 2:
If the sql statements are like
BEGIN TRAN

SQL Statements

END TRAN

SET “RetainSameConnection” property on the connection manager to true. This will fix the problem.

Reference : Pinal Dave (http://blog.SQLAuthority.com)