Working on a SQL 2017 database where an external, non-Microsoft application has direct access to read/write data via the ODBC Driver 17 for SQL Server.
They suddenly began to experience a regression, UPDATE statements were failing with the following message snippet:
esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
UPDATE failed because the following SET options have incorrect settings:
'ANSI_WARNINGS'. 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.
esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
Right away, I figured that something in the database from the above list of options was incompatible with the legacy ANSI_WARNINGS = OFF setting the external developers were using in their code. And I was resigned quickly to the fact that something in SQL Server would have to change, as this project was nearing a time-sensitive milestone.
Though ANSI_WARNINGS = OFF is not recommended, changing it in an existing application is problematic. Development should begin with ANSI_WARNINGS = ON and ANSI_DEFAULTS = ON, but if it doesn't, changing these settings requires a LOT of regression testing, especially around the behavior of NULL values in aggregate functions, divide-by-zero and artithmetic error handling, and how trailing blanks are handled. So yeah, it gets messy and time-consuming.
As the error message above had said, I quickly looked through the database for the SQL features listed in the error message (above). All of these features of SQL Server have incompatibilities with the legacy ANSI_WARNINGS = OFF setting.
Sure enough, we had added a very beneficial filtered index recently to help with some lookups on a table where only a minority of the records had a status we were interested in.
The filter was removed, performance gain lost, the UPDATE statements started working again.