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

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


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 |