1

My question is somewhat related to UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' .

I am using SQL Query Notifications to enable caching for my application from the database and have run into a strange behaviour that I can't fully explain.

If I run some SQL query notification code 

using (SqlCommand command=new SqlCommand("SELECT MyColumn1, MyColumn2, etc... FROM dbo.StockSupplierCode", connection))
{
    SqlDependency dependency=new SqlDependency(command);
    dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
    command.ExecuteReader();    
}

After running this the first time on my database if I try to update the affected table I get the error:

Msg 1934, Level 16, State 1, Procedure Stock_NonIntTrigger, Line 14 [Batch Start Line 0] UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The error specifically refers to it occuring in a named trigger in my database called Stock_NonIntTrigger.

This error will now persist anytime I subsequently try to update this table.

HOWEVER if I open this trigger, change nothing and resave it, the error disappears.

My only assumption is that when I resave this trigger that SQL Query Analyser is perhaps adding the QUOTE_IDENTIFIER ON syntax HOWEVER when I reopen the same trigger using sp_helptext it appears unaffected and identical to the original.

Yet the updates will now succeed.

Anyone able to provide any insight into:

  1. Why might Query Notifications be causing this error?
  2. Why does resaving the trigger make the error go away?
Maxim Gershkovich
  • 45,951
  • 44
  • 147
  • 243

1 Answers1

2

The QUOTED_IDENTIFIER and ANSI_NULL session settings in effect at the time of trigger creation are persisted as object meta-data. These are used at run time and override the current session settings within the trigger scope. This behavior also applies to stored procedures, functions, and views.

Take care to ensure both QUOTED_IDENTIFIER and ANSI_NULL are ON when creating objects. These options are on by default in SSMS but not with SQLCMD for backwards compatibility reasons. I suggest one always specify the -I SQLCMD argument to turn on quoted identifiers to avoid this gotcha.

Regarding Query Notifications, SQL Server requires the "magic 7" session options to be set properly to use this feature. These are the same options required for other features like indexed views and filtered indexes:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON

All of these options are set properly by default by modern SQL Server APIs with SQL Server 2005+ database compatibility levels. However, persisted settings are a common problem due to inattention to detail.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • HI Dan, Thanks so much for your input and specifically for pointing out the 'magic 7' (as you put it). I must have read that requirement a hundred times and still not processed it. I have found the option located in SELECT name = OBJECT_NAME([object_id]), uses_quoted_identifier FROM sys.sql_modules which appears to change after I resave the trigger from Query Analyser. – Maxim Gershkovich May 21 '20 at 10:19
  • @MaximGershkovich So the question now becomes who/what created that trigger with the incorrect settings. That should not happen "by accident". And if there is one trigger created with these settings, there are likely others (including stored procedures) that also have this problem. – SMor May 21 '20 at 12:29
  • When being called externally turns out we have to manually set these in the trigger. I had never experienced this before. This post was 100% the answer. Thank you. – Eva Donaldson Sep 25 '20 at 18:22