Skip to Content

 

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

11 Comments

  • Paul Nielsen

    Feb 17, 2017 at 05:09 PM

    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!

  • Feb 17, 2017 at 05:25 PM

    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!

  • Feb 17, 2017 at 05:36 PM

    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

  • Feb 18, 2017 at 09:26 AM

    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

  • Feb 18, 2017 at 07:58 PM

    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

  • Feb 19, 2017 at 10:50 AM

    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

  • Feb 19, 2017 at 06:21 PM

    @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.

  • Feb 20, 2017 at 09:30 AM

    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.

  • Tim Cartwright

    Feb 21, 2017 at 01:19 PM

    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.

  • Rob Boek

    Feb 25, 2017 at 01:30 AM

    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

  • Ernest

    Mar 01, 2017 at 05:34 AM

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

Receive comment updates via RSS

Leave a Comment