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
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 OFForANSI_NULLS OFF. Those two settings are saved with the module at create time. - An older client library or tool that connects with
ARITHABORTor other options off. - A SQL Agent job or
sqlcmdscript with different defaults from SSMS.
How to fix it
-
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;
-
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;
-
For
sqlcmd, pass-Ito turnQUOTED_IDENTIFIERon.
Related errors
More syntax and compilation errors
156Incorrect syntax near the keyword105Unclosed quotation mark after the string4145Non-boolean expression in a condition8120Column not in GROUP BY or aggregate8118Aggregate without GROUP BY8672MERGE touched the same row twice
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.
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.