none
SET NOEXEC ON/OFF scope RRS feed

  • Question

  • Hello,

    could the "set noexec" off the following example have an effect on other statements running parallel?

    SET NOEXEC ON; statement xy; SET NOEXEC OFF;

    It's fired by an app per db.runSql("set noexec on; statement xy; set noexec off");

    Tuesday, June 25, 2019 4:41 AM

All replies

  • Hi user34434,

     

    Welcome to SQL ServerTransact-SQL.

     

    The execution of statements in SQL Server has two phases: compilation and execution. When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

     

    In following script , I will explain the effect for SQL statements. In the first script , we will get the result normally. In the second script , I will drop test first and then  SET NOEXEC ON. After that , I create table and insert some simple data , and then SET NOEXEC OFF and  it will show an error .

    The cause of this problem is that the script of CREATE TABLE and INSERT INTO just compile and not execute. It  will not actually create the 'test' table.

     
    SET NOEXEC OFF;  
    go
    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go
    create table test (
    A int 
    )
    insert into test values (1),(2)
    GO  
    SELECT * from test
    /*
    A
    -----------
    1
    2
    */
    GO  
    
    
    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go
    -- SET NOEXEC to ON.  
    SET NOEXEC ON;  
    GO  
    create table test (
    A int 
    )
    insert into test values (1),(2)
    GO  
    -- SET NOEXEC to OFF.  
    SET NOEXEC OFF;  
    GO  
    SELECT * from test 
    /*
    Msg 208, Level 16, State 1, Line 33
    Invalid object name 'test'.
    */
    GO  

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 7:31 AM
  • Hello,

    thanks for your explanation of the bahaviour of noexec. Unfortunately I'am not sure if it's the answer to my question. Maybe I was not exact enough. I try it in other words: I fire n billion inserts in a while loop. In between this, I fire my example line above in parallel. Could it by, that some of the inserts did not happen? Because set noexec operates server wide?

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-2017

    says these setting changes the current session handling. I'am not sure what that means. What I want is, that set "noexec on; statement x; noexec off" only has an effect on this particular statement in between the noexec on/off and NOT on any statements else.


    • Edited by user34434 Tuesday, June 25, 2019 9:19 AM
    Tuesday, June 25, 2019 9:19 AM
  • Hi user34434,

     

    Thank you for your kind reply.

     

    If you have many INSERT INTO between SET NOEXEC ON and SET NOEXEC OFF,  as I said above 'When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them', the data will not be inserted into your target table .

     

    And 'these setting changes the current session handling'. Please try to use sp_who and it will provide information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 9:57 AM
  • thanks for your explanation of the bahaviour of noexec. Unfortunately I'am not sure if it's the answer to my question. Maybe I was not exact enough. I try it in other words: I fire n billion inserts in a while loop. In between this, I fire my example line above in parallel. Could it by, that some of the inserts did not happen? Because set noexec operates server wide?

    SET NOEXEC ON affects only the current process. (And this is true for all SET statements.) So the other INSERT statements are not affected.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 10:17 PM
  • Hi user34434,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 28, 2019 9:43 AM