// SQL Server error reference

SQL Server error 1934: SET options have incorrect settings

A statement touched an indexed view, filtered index, computed column index or XML method with the wrong SET options, usually QUOTED_IDENTIFIER or ANSI_NULLS off.

Error number
1934
Severity
16
Category
Syntax and compilation
Error message
%ls failed because the following SET options have incorrect settings: '%.*ls'. Verify that SET options are correct for use with %S_MSG.

What it means

Indexed views, filtered indexes, indexes on computed columns, XML data type methods and some other features require specific session settings, mainly ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT and CONCAT_NULL_YIELDS_NULL ON, and NUMERIC_ROUNDABORT OFF. If a session (or a stored procedure, which keeps the settings it was created with) has them wrong, the statement fails with 1934, which names the offending options.

Common causes

  • A stored procedure or trigger created with QUOTED_IDENTIFIER OFF or ANSI_NULLS OFF. Those two settings are saved with the module at create time.
  • An older client library or tool that connects with ARITHABORT or other options off.
  • A SQL Agent job or sqlcmd script with different defaults from SSMS.

How to fix it

  1. Set the required options before the statement.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_PADDING ON;
    SET ANSI_WARNINGS ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT OFF;
  2. Recreate procedures and triggers with the right settings, since they keep the values from when they were created.

    SELECT OBJECT_NAME(object_id) AS module, uses_ansi_nulls, uses_quoted_identifier
    FROM sys.sql_modules WHERE uses_ansi_nulls = 0 OR uses_quoted_identifier = 0;
  3. For sqlcmd, pass -I to turn QUOTED_IDENTIFIER on.

Related errors

More syntax and compilation errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.