Implementing Transactions in SQL Server – Part II
This statement defines an explicit transaction for the entire sql script.
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.
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.
Pages: 1 2