Databases
StackOverflow2013
Postsdbo
POInteresting behavior in "NOEXEC ON"
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInteresting behavior in "NOEXEC ON"
    primarykey
    data
    text
    <p>While I was writing some T-SQL queries with <code>NOEXEC ON</code>, I experienced interesting behavior of SQL Server and I am curious about why it happened. Sometimes I got only </p> <blockquote> <p>Command(s) successfully.</p> </blockquote> <p>message as I expected, but sometimes I got one or more </p> <blockquote> <p>(0 row(s) affected)</p> </blockquote> <p>messages.</p> <p>I know that <code>SET NOEXEC ON</code> command compiles query but does not execute it, so I think I should not gotten any </p> <blockquote> <p>(0 row(s) affected)</p> </blockquote> <p>messages.</p> <p>In the first example, everything looks normal.</p> <pre><code>SET NOEXEC ON INSERT INTO Test (column1) VALUES ('etc') </code></pre> <p>Result:</p> <blockquote> <p>Command(s) successfully.</p> </blockquote> <p>But in the second example, I think something goes wrong...</p> <pre><code>SET NOEXEC ON DELETE FROM Test </code></pre> <p>Result:</p> <blockquote> <p>(0 row(s) affected)</p> </blockquote> <p>In the third example I used temp table:</p> <pre><code>CREATE TABLE #tmp (id INT IDENTITY(1, 1), idX INT) SET NOEXEC ON INSERT INTO #tmp (idX) VALUES (1) DELETE FROM Test SET NOEXEC OFF DROP TABLE #tmp </code></pre> <p>Result:</p> <blockquote> <p>(0 row(s) affected)</p> </blockquote> <p>And finally I added only <code>GO</code> to my query, I think result is interesting</p> <pre><code>CREATE TABLE #tmp (id INT IDENTITY(1, 1), idX INT) SET NOEXEC ON GO INSERT INTO #tmp (idX) VALUES (1) DELETE FROM Test SET NOEXEC OFF DROP TABLE #tmp </code></pre> <p>Result:</p> <blockquote> <p>(0 row(s) affected)</p> <p>(0 row(s) affected)</p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USogun
    1. USogun
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInteresting behavior in "NOEXEC ON"
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POInteresting behavior in "NOEXEC ON"
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POInteresting behavior in "NOEXEC ON"
      1. USogun
      1. VTBountyStart
    1. COHm I can't repro any case where I get "(0 row(s) affected)". Neither in Denali nor in SQL Server 2008. Did you just execute these SQL texts using SSMS without any special options or actions?
      singulars
      1. POInteresting behavior in "NOEXEC ON"
      1. USusr
    2. COYes, I executed these SQL texts using SSMS on SQL Server 2008 R2 and I did not used any special options.
      singulars
      1. POInteresting behavior in "NOEXEC ON"
      1. USogun
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload