SET Options Have Incorrect Settings (Error 1934)

The message is unusually helpful once you read it properly. It names the statement type that failed and the exact option that is wrong. The long list at the end is not a list of your problems, it is the list of features that demand these settings, and you only have one of them.

Why It Works in SSMS and Fails Everywhere Else

This is the whole story, and it is not about your code at all. Different clients connect with different defaults. SSMS sets QUOTED_IDENTIFIER ON. sqlcmd does not: its own reference gives the default as OFF, and -I is the switch that turns it on.

I hit this while writing this very post: the setup script failed at CREATE INDEX before it got anywhere near the demonstration, because sqlcmd had QUOTED_IDENTIFIER off and I had not thought about it. That is exactly how it reaches production. The developer tests in SSMS, it works. The SQL Agent job step, the sqlcmd script, the older ODBC driver or the linked server runs the identical statement and it fails.

-- what the CURRENT connection actually has
SELECT  quoted_identifier, ansi_nulls, ansi_padding, ansi_warnings,
        arithabort, concat_null_yields_null,
        -- not a column of the DMV, so read it off the session's option bits
        CASE WHEN @@OPTIONS & 8192 = 8192 THEN 1 ELSE 0 END AS numeric_roundabort
FROM    sys.dm_exec_sessions
WHERE   session_id = @@SPID;

Run that from SSMS and again from whatever is actually failing. The row that differs is your answer.

What It Fails On, and What It Does Not

Verified on SQL Server 2025 CU8 against a table carrying a filtered index. This is the table, and the CREATE INDEX is the statement that needs QUOTED_IDENTIFIER ON to run at all:

SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE dbo.Orders (OrderID INT PRIMARY KEY, Status VARCHAR(20), Total INT);
CREATE INDEX ix_orders_open ON dbo.Orders(OrderID) WHERE Status = 'Open';

Now turn the option off and write to it:

SET QUOTED_IDENTIFIER OFF;
GO
INSERT INTO dbo.Orders (OrderID, Status) VALUES (1, 'Open');   -- Msg 1934
GO
UPDATE dbo.Orders SET Total = 99 WHERE OrderID = 1;            -- Msg 1934
GO
SELECT COUNT(*) FROM dbo.Orders;                               -- fine

Reads succeed. Only writes fail. DELETE fails the same way, and the message opens with the statement type, so it reads DELETE failed because... rather than INSERT failed because.... That is worth knowing because it shapes how the problem is reported to you: reporting works, the application looks healthy, and one nightly job fails. The index has to be maintained on write, and SQL Server refuses to maintain it from a session whose options would change what the filter means.

The option named in the message changes with the offender. With ANSI_NULLS OFF the identical insert fails naming ANSI_NULLS instead. Both were reproduced.

The Settings It Wants

For filtered indexes, indexed views and indexes on computed columns, 6 options must be ON and 1 OFF:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;      -- note: OFF, not ON

NUMERIC_ROUNDABORT is the one people set wrongly when pasting a block like this, because it breaks the pattern. Of the 7, QUOTED_IDENTIFIER and ANSI_NULLS are the 2 I reproduced failures for directly; the rest are the documented requirement.

The Trap: a Procedure Carries Its Own Options

Fixing your session does not fix a stored procedure. A module captures these settings when it is created and uses them for ever, whatever the calling session has.

SET QUOTED_IDENTIFIER OFF;
GO
CREATE PROCEDURE dbo.usp_AddOrder AS
    INSERT INTO dbo.Orders (OrderID, Status) VALUES (9, 'Open');
GO
SET QUOTED_IDENTIFIER ON;
GO
EXEC dbo.usp_AddOrder;   -- STILL Msg 1934

Verified: created that way, the procedure reports OBJECTPROPERTY(..., 'ExecIsQuotedIdentOn') = 0, and calling it from a session with everything set correctly fails anyway. The fix is to recreate the procedure with the right options in effect, not to change anything about the caller.

-- find every module baked with the wrong setting
SELECT  OBJECT_SCHEMA_NAME(object_id) AS [schema],
        OBJECT_NAME(object_id)        AS object_name,
        OBJECTPROPERTY(object_id, 'ExecIsQuotedIdentOn') AS quoted_identifier_on,
        OBJECTPROPERTY(object_id, 'ExecIsAnsiNullsOn')   AS ansi_nulls_on
FROM    sys.sql_modules
WHERE   OBJECTPROPERTY(object_id, 'ExecIsQuotedIdentOn') = 0
   OR   OBJECTPROPERTY(object_id, 'ExecIsAnsiNullsOn')   = 0;

Anything that query returns is a future 1934 waiting for someone to add a filtered index. Worth running before you add one.

TRY/CATCH Will Not Help

Do not plan to swallow this one. 4 separate attempts inside TRY blocks all produced a raw error and aborted the batch, with the CATCH block never running. The one shape that is caught is the statement wrapped in sp_executesql, because it then compiles as its own batch and ERROR_NUMBER() comes back as 1934; that is a test harness, not a fix. It behaves like Msg 213: a statement-level failure rather than a runtime error you can trap.

Microsoft’s reference covers SET QUOTED_IDENTIFIER, the SET options indexed views and computed-column indexes require and SET ANSI_NULLS in full.

Common Questions

Related Scripts

• Column Name or Number of Supplied Values (Error 213), the other statement-level failure TRY/CATCH will not catch
• Get Index Fragmentation Across Databases, what indexes you actually have
• Silent Failures, the family this belongs to: works here, fails there
• SQL Server Errors: The Complete Guide, the rest of the library