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 816780
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.
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?

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?

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?

Problem creating indexed views with linked servers

Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.

Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A

The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!Ariel (ap@.data54.com) writes:
> Hi, I have a problem, maybe someone can help me.
> I'm traing to create a view with a Linked Server

Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp