THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Stopping Execution of Future Batches

 

There is a command that as far as I can tell has been in the product since SQL2008 but I was woefully unaware of. Actually I find many others are unaware as well and hence the blog post. The command is SET NOEXEC which has options for ON or OFF.  Normally this is OFF by default and the issue I was trying to solve was this:

I have a TSQL Script with N individual batches separated by a GO. Or simply N many individual statements or sets of statements that I don’t ever want to be executed all at once. In real life I often have a single script with lots of pieces (batches) vs. many individual scripts for convenience, ease of use etc. However I don’t want to accidently run the entire script or any other statements that I don’t manually highlight and run. A simple example is shown below:

PRINT 'Got Here 1' ;
GO
PRINT 'Got Here 2' ;
GO
PRINT 'Got Here 3' ;
GO
PRINT 'Got Here 4' ;
GO

If we were to hit F5 (or however you execute your TSQL statements in SSMS) without highlighting any statement(s) they would all be executed, one batch after the other. Even if one batch were to fail or we had a THROW in that batch it would fail at that point but execution would continue immediately after the next GO until the end. This is where SET NOEXEC ON comes into play. If I add that at the beginning of the script all succeeding code would not be executed. The statements would only be compiled and not actually run. It would look like this:

SET NOEXEC ON;

PRINT 'Got Here 1' ;
GO
PRINT 'Got Here 2' ;
GO
PRINT 'Got Here 3' ;
GO
PRINT 'Got Here 4' ;
GO

And if needed you could always set it back OFF as the example below show:

SET NOEXEC ON;

PRINT 'Got Here 1' ;
GO
PRINT 'Got Here 2' ;
GO
PRINT 'Got Here 3' ;
GO
SET NOEXEC OFF;

PRINT 'Got Here 4' ;
GO

If you run each of these in SSMS you will quickly see how this setting affects the actual execution of the statements in the batches. Adding this command at the top of your scripts will help to prevent unwanted execution of the code in that script whether it be the entire set of statements or any individual ones. Once this is in the script you would either have to comment out the SET NOEXEC ON statement or manually highlight the statement or set of statements you want to run before hitting F5. I am pretty confident that many of you will find this tip useful. I know I did and wish I knew about it much earlier.

Good luck,

Andy

Published Friday, February 17, 2017 3:09 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Nielsen said:

This is great. I think I read this once but never made it a function part of my thinking. For the past couple of years I've had a script that really needs this. Many Thanks!

February 17, 2017 5:09 PM
 

cinahcaM madA said:

FYI I think NOEXEC has been around since at least as early as SQL Server 7.0 -- I believe the "parse" option in QA/SSMS used/uses it. Really good idea to use it in this alternative way though!

February 17, 2017 5:25 PM
 

cinahcaM madA said:

I was just reading up on NOEXEC and discovered another set option that might be better for this purpose: PARSEONLY. Apparently NOEXEC does some object validation, whereas PARSEONLY doesn't do anything but make sure the syntax is valid. So maybe it would be a bit faster, if that matters?

Example:

---

SET NOEXEC ON

SELECT * FROM fn_dblog

--

vs

---

SET PARSEONLY ON

SELECT * FROM fn_dblog

---

The first generates an error; the second doesn't.

--Adam

February 17, 2017 5:36 PM
 

Andrew Kelly said:

Adam, Yes I did some looking afterwards as well and it appears both NOEXEC and PARSEONLY will stop the execution and give the desired results. PARSEONLY checks syntax and object references but does not compile where as NOEXEC does compile. So while it is true PARSEONLY may be a little bit faster for the few times a year when this accidently gets run are a few ms really an issue :). But to me NOEXEC is pretty clear as to what its intent is so I will stick with that.

Andy

February 18, 2017 9:26 AM
 

Greg Low said:

This is useful but still doesn't address my most common use case. For me, it's not a case of running a whole script accidentally, it's that if any part of the script fails, I don't want the rest to run. There really should be an option in SSMS to just stop that.

Regards,

Greg

February 18, 2017 7:58 PM
 

Andrew Kelly said:

Greg,

I agree there should be a built in way to do that. However I think you can accomplish that by wrapping the entire script in a BEGIN TRAN and setting XACT_ABORT ON. Yes I know it is not perfect but it will stop execution at that point and rollback any open trans.

Andy

February 19, 2017 10:50 AM
 

cinahcaM madA said:

@Andy

I think you need implicit transactions rather than BEGIN TRAN to make that work. Otherwise, e.g.:

Batch 1 hits an error, gets aborted

Batch 2 runs, auto-commits (because there is no tran)

At least with implicit transactions you'd have a second open transaction and would be able to roll it back.

February 19, 2017 6:21 PM
 

Andrew Kelly said:

Adam,

I thought that XACT_ABORT stopped all execution when it encountered an error but I just tried and you are correct in that it will proceed after the next GO so that won't work for multiple batches. I should have tested it first.

February 20, 2017 9:30 AM
 

Tim Cartwright said:

This is exactly how Red-Gate sql compare has done their upgrade scripts from the beginning. Very useful technique. They combine it with XACT_ABORT ON to ensure its an all or nothing process.

February 21, 2017 1:19 PM
 

Rob Boek said:

I use SSDT quite a bit and they add the following to scheme compare scripts. They use ":on error exit" which is sqlcmd syntax that prevents additional batches from running if there is an error (Greg was asking for this above). They combine it with "SET NOEXEC ON;" if sqlcmd is not enabled.

:on error exit

GO

/*

Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.

To re-enable the script after enabling SQLCMD mode, execute the following:

SET NOEXEC OFF;

*/

:setvar __IsSqlCmdEnabled "True"

GO

IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'

   BEGIN

       PRINT N'SQLCMD mode must be enabled to successfully execute this script.';

       SET NOEXEC ON;

   END

GO

February 25, 2017 1:30 AM
 

Ernest said:

Greg, do you have a Connect item for this feature that could be upvoted?

March 1, 2017 5:34 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement