Finding Procedures Created With the Wrong SET Options

A procedure runs for years, then an indexed view or filtered index change exposes error 1934 about SET options. The procedure can carry wrong SET options from the session that created it. sys.sql_modules records its ANSI_NULLS and QUOTED_IDENTIFIER settings, so the audit can find affected modules before the next failure.

Understand What Creation Captures

SQL Server saves ANSI_NULLS and QUOTED_IDENTIFIER settings with a stored procedure when it is created or altered. Later callers do not change those saved values by setting their own session options for the static SQL in that module. An old deployment tool can create the module under OFF settings, and a later index or computed column rule can reveal the problem.

I have found the session setting in a deployment script rather than in the application call that raised the error. Who last altered the procedure, and what tool ran that script? That history helps fix the source of the wrong SET options, not just one compiled module.

Find Procedures Saved With the Wrong SET Options

Query sys.sql_modules and join sys.objects to list procedures whose saved flags are off. Expand the object-type filter if views, functions, or triggers are in scope. Metadata visibility follows permissions; a missing row is not proof that every module is clean.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
       o.name AS module_name, o.type_desc,
       m.uses_ansi_nulls,
       m.uses_quoted_identifier,
       o.modify_date
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.type IN ('P','PC')
  AND (m.uses_ansi_nulls = 0
       OR m.uses_quoted_identifier = 0)
ORDER BY schema_name, module_name;

A CLR procedure has different body handling, so focus the repair on T-SQL modules whose source can be reviewed. Add definition text or OBJECT_DEFINITION when you need the full statement. Encrypted modules require the authoritative source file because their definitions are not exposed in the catalog.

Reproduce the Creation Path in a Lab

In a test database, set the options OFF, create a small procedure, and query the flags. Then ALTER it under ON settings and read the flags again. This shows that the relevant time is module creation or alteration. Do not run the OFF portion against production merely to demonstrate an error.

SET ANSI_NULLS OFF;
GO
SET QUOTED_IDENTIFIER OFF;
GO
CREATE OR ALTER PROCEDURE dbo.DemoSavedOptions
AS SELECT 1 AS value;
GO
SELECT uses_ansi_nulls, uses_quoted_identifier
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.DemoSavedOptions');
GO

The saved flag changes behavior, not only metadata. In my lab, a procedure created with ANSI_NULLS OFF returned equal for NULL = NULL, even when called from a session with ANSI_NULLS ON. The catalog flag remains the audit source, and a NULL comparison test only shows the effect.

Connect Wrong SET Options to Error 1934

Indexed views, indexes on computed columns, filtered indexes, and related features require a set of session options. Error 1934 names options that are incorrect for an operation. ANSI_NULLS and QUOTED_IDENTIFIER are two members of that set; ANSI_WARNINGS, ANSI_PADDING, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and NUMERIC_ROUNDABORT also matter. The module audit covers saved creation settings, not every runtime session option. In the same lab, a procedure saved with QUOTED_IDENTIFIER OFF inserted into a table with a filtered index. It failed with error 1934, although the calling session had the option ON.

DBCC USEROPTIONS;

Read the error message's exact option list and test the failing statement in a safe copy. A procedure with the two saved flags ON can still fail because the caller or job session has another required option wrong. Avoid the shortcut of blaming every 1934 on QUOTED_IDENTIFIER. The error and module metadata together identify the actual repair.

Recreate the Reviewed Module Correctly

Place SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON before a batch containing CREATE OR ALTER PROCEDURE. Use the complete approved definition, not an empty replacement body. Script permissions and dependencies, review the plan impact, and deploy in a controlled window. Recheck sys.sql_modules afterward.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.DemoSavedOptions
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 1 AS value;
END;
GO
SELECT uses_ansi_nulls, uses_quoted_identifier
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.DemoSavedOptions');

CREATE OR ALTER can preserve permissions better than DROP and CREATE, but the reviewed definition must still be the full intended procedure. Compile and execute representative paths in a restored copy. An ALTER can also trigger recompilation and a different plan, so measure critical routines after deployment.

Fix the Deployment Source

If the deployment tool keeps setting QUOTED_IDENTIFIER OFF, a repaired module can become wrong again at the next release. Add explicit ON statements to the source scripts and make the pipeline check the catalog flags after deployment. Record exceptions with a reason and owner. Do not silently change compatibility-sensitive code without testing its quoted identifiers and NULL semantics.

I keep a before list, the exact source definition used for each ALTER, and an after query showing both flags ON. For the error that prompted the work, rerun the failing operation against representative data and check the related index or view behavior. A clean catalog flag is a necessary check, not a complete application test.

Identify the Full SET Contract

The two module flags are easy to audit because SQL Server stores them in sys.sql_modules. Other required options live in the execution environment and can be set by the connection, application library, or job step. Record the failing session's option state as close to the incident as possible. A reproduction on a different SSMS connection can succeed simply because that client starts with different defaults.

For indexed views and computed-column indexes, SQL Server enforces the required option combination during relevant DML and optimizer use. Read the exact error text and object dependency. A procedure that reads a filtered index can behave differently from one that updates its table. Test the path that failed, with the same application connection settings and representative data.

Review Module Dependencies Before ALTER

Changing a procedure's saved options means altering it. The approved source definition can differ from the text currently installed, especially when emergency fixes were applied directly. Diff the live definition against the maintained file and resolve that discrepancy before deployment. Keep permissions, signatures, execution context, and related jobs in the review. A signed module can require re-signing after ALTER.

I avoid a blind loop that recreates every flagged module. One procedure can rely on quoted identifier behavior or have a fragile dynamic SQL branch. Fix the source, test the full procedure, and then deploy a bounded set. The catalog query supplies candidates; it is not an automatic repair script.

Prevent Wrong SET Options at the Next Release

Add SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON to the module template, and validate those flags after each release. If a release tool strips the statements or uses an unexpected connection mode, the post-deployment check will catch it. Store the before and after lists with the deployment record. That turns a future error 1934 from a mystery into a failed, visible release gate.

Related reading on this blog: Fix: Error: 1934, Level 16, INSERT or UPDATE Failed Because the Following SET Options have Incorrect Settings: 'QUOTED_IDENTIFIER'. and How Does QUOTED_IDENTIFIER Works in SQL Server? Interview Question of the Week #217.

A module saved under the wrong SET flags is not fixed by a comment, it is fixed by redeployment.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.