Begin Transaction
This statement defines an explicit transaction for the entire sql script.
If Xact_State()=1
This statement checks for any committable
transactions at the end of the script. If there are any, then Xact_State() will be
1, and the transaction will be committed in the IF block.
If Xact_State()=-1
This statement checks if the transaction introduced in the script is in an ‘uncommittable’ state. By uncommittable, I mean if any
error was encountered during the execution of the script then the script cannot
continue execution, and all changes introduced in the database up to the point
of failure in the script need to be rolled back. This rollback is done in this
IF block.
Please note that scripts in which we introduce
transactions in this manner, can only be run from the command prompt via SQL
Server’s command-line utility sqlcmd. If this script is run from Management
Studio, then it is possible that it will run only partially and commit only those changes
where there are no errors in the script. This is because there is a difference
in the behavior of execution of sql scripts when they are run from the SQL
Server Management Studio, and when they are run from the command prompt since SSMS uses the .NET Framework SqlClient
for execution in regular and SQLCMD mode in Query Editor . Whereas, sqlcmd
when run from the command line uses the OLE DB provider. Since different
default options may apply, there are different behaviors when executing
the same query in SSMS in SQLCMD Mode and when exuting the query using the sqlcmd
utility
This solution is good for implementing transactions in individual
scripts when they are called from sqlcmd. But what if there is a requirement to
run a series of such scripts which should all either commit or all
roll back it is not a good solution.
We have to tweak this approach a little bit to satisfy this
requirement. For this purpose we could not define the transactions in individual
scripts as I have explained earlier, but call these scripts from
another sql script I call as Wrapper.sql, then define the transaction in this
wrapper and call the wrapper from sqlcmd. The script would like this:
:On Error Exit
SET XACT_ABORT ON
GO
Begin Transaction
:r script_1.sql
:r script_2.sql
:r script_3.sql
If Xact_State()=1
Begin
Print 'Committing Tranaction...'
Commit tran
End
Else If Xact_State()=-1
Begin
Print 'Rolling Back Transaction...'
RollBack Tran
End
Throughout this example the concept has remained the same. The only difference is how and
where to define the transaction, and how to call the scripts.
]]>