How to commit and rollback transaction in sql server? <p>I have a huge script for creating tables and porting data from one server. So this sceipt basically has - </p> <ol> <li>Create statements for tables.</li> <li>Insert for porting the data to these newly created tables.</li> <li>Create statements for stored procedures. </li> </ol> <p>So I have this code but it does not work basically @@ERROR is always zero I think..</p> <pre><code>BEGIN TRANSACTION --CREATES --INSERTS --STORED PROCEDURES CREATES -- ON ERROR ROLLBACK ELSE COMMIT THE TRANSACTION IF @@ERROR != 0 BEGIN PRINT @@ERROR PRINT 'ERROR IN SCRIPT' ROLLBACK TRANSACTION RETURN END ELSE BEGIN COMMIT TRANSACTION PRINT 'COMMITTED SUCCESSFULLY' END GO </code></pre> <p>Can anyone help me write a transaction which will basically rollback on error and commit if everything is fine..Can I use <a href="http://msdn.microsoft.com/en-us/library/ms178592.aspx">RaiseError</a> somehow here..</p>