Can Status of NOEXEC be Checked with a DMV? On larger DDL scripts for deployments, usage of SET NOEXEC ON as part of error handling is commonplace to prevent execution of downstream code. If coupled with SET NOCOUNT ON, there's a chance code execution may be inadvertently suppressed unless the proper post-deployment checks are done. I checked sys.dm_exec_sessions and there is no attribute for either SET statement. Is there an alternate method of checking the status of either statement for the current session or any user's session from the DBA perspective?

Here's a quick example...

CREATE TABLE dbo.MJZTest
    (Col1 int)
ON [PRIMARY2]
GO
IF @@ERROR&lt;&gt;0
    SET NOEXEC ON

INSERT INTO dbo.MJZTest (Col1)
VALUES (1)
GO


In the above example, if the [PRIMARY2] filegroup doesn't exist, preventing table creation, the @@ERROR check will call the SET NOEXEC ON statement which will prevent actual execution of the subsequent INSERT statement. I've seen this type of code generation with tools such as RedGate SQL Compare.

The catch is, if the person executing the code neglects to run SET NOEXEC OFF to reset the session during debugging, they may get a Command(s) completed successfully. message regardless of the actual outcome.
NOCOUNT can be determined for the current session by parsing @@OPTIONS or by running DBCC USEROPTIONS.
Unfortunately, the only way I could find to determine the value of this option for a different session is by capturing the ExistingConnection event in SQLTrace or Extended Events.

Example 1:

DBCC USEROPTIONS


Output:

+-------------------------+----------------+
|       Set Option        |     Value      |
+-------------------------+----------------+
| textsize                | 2147483647     |
| language                | us_english     |
| dateformat              | mdy            |
| datefirst               | 7              |
| lock_timeout            | -1             |
| quoted_identifier       | SET            |
| arithabort              | SET            |
| ansi_null_dflt_on       | SET            |
| ansi_warnings           | SET            |
| ansi_padding            | SET            |
| ansi_nulls              | SET            |
| concat_null_yields_null | SET            |
| isolation level         | read committed |
+-------------------------+----------------+


Example 2:

SELECT *
FROM (
    VALUES  
    (1     ,'DISABLE_DEF_CNST_CHK'),
    (2     ,'IMPLICIT_TRANSACTIONS'),
    (4     ,'CURSOR_CLOSE_ON_COMMIT'),
    (8     ,'ANSI_WARNINGS'),
    (16    ,'ANSI_PADDING'),
    (32    ,'ANSI_NULLS'),
    (64    ,'ARITHABORT'),
    (128   ,'ARITHIGNORE'),
    (256   ,'QUOTED_IDENTIFIER'),
    (512   ,'NOCOUNT'),
    (1024  ,'ANSI_NULL_DFLT_ON'),
    (2048  ,'ANSI_NULL_DFLT_OFF'),
    (4096  ,'CONCAT_NULL_YIELDS_NULL'),
    (8192  ,'NUMERIC_ROUNDABORT'),
    (16384 ,'XACT_ABORT')
) AS v(num, descr)
WHERE @@OPTIONS &amp; num = num;


Output:

+------+-------------------------+
| num  |          descr          |
+------+-------------------------+
|    8 | ANSI_WARNINGS           |
|   16 | ANSI_PADDING            |
|   32 | ANSI_NULLS              |
|   64 | ARITHABORT              |
|  256 | QUOTED_IDENTIFIER       |
|  512 | NOCOUNT                 |
| 1024 | ANSI_NULL_DFLT_ON       |
| 4096 | CONCAT_NULL_YIELDS_NULL |
+------+-------------------------+


Unfortunately, I could not find a way to tell whether NOEXEC is on or off.