Implementing Transactions in SQL Server – Part II

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
Begin Transaction
:r script_1.sql
:r script_2.sql
:r script_3.sql
If Xact_State()=1
       Print 'Committing Tranaction...'
              Commit tran
Else If Xact_State()=-1
       Print 'Rolling Back Transaction...'
              RollBack Tran

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

Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |