Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT
'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?