How does SQLServer execute scripts?

Nov 25, 2007 at 5:16 PM
Edited Nov 25, 2007 at 6:04 PM
The last week I found some problems when executing scripts using the version Some scripts were executed but the database was not modified. I could not understand what was going on, I profiled the execution and verified that the scripts were executed.

After a while we found that one script was starting a transaction which was never commited or rollback. Another peculiar case was when a BEGIN ... COMMIT section was failing because a constraint which finished the execution of the script in the middle leaving the transaction open. Although these two cases are related to bad practices writting SQL scripts, I found the application should be able to handle these problems.

Following is a very brief overview how SQL Runner currently executes scripts:

  1. A single connection is open to the database before the scripts are executed
  2. All scripts are executed using the same connection which explain the problems I mentioned above
  3. If a script begins a transaction and it does not commit/rollback then the transaction stays open for the next script
  4. A open transaction is automatically rollback when the connection is closed, which happens at the end of the execution of the scripts explaining why we could not see the updates

As a result of these findings I am working in new version which will check for the mentioned problems so people dont encounter the similar problems as I did last week.

Any comments or suggestions are welcome. Thanks.