5

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

MattyZDBA
  • 1,955
  • 3
  • 20
  • 32

2 Answers2

2

You can add something like this to the end of your script:

declare @noexec_check int = 1;
set noexec off;
if @noexec_check is null
begin
   raiserror('Script ended with NOEXEC ON',16,1);  
   set noexec on;
end

Similar to how you check for FMTONLY using control flow.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
1

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

spaghettidba
  • 11,376
  • 31
  • 42