🚀 go-pugleaf

[posted and mailed, please reply in news]

[Note: I've set followups to comp.databases.ms-sqlserver only.]

^-^ (mortalnightmare@hotmail.com) writes:
> I have a table called dc_weighbridge_MAIN that that has to have an
> unique id called [ticket] but "ticket" also has to allow null values...
> so i set the ticket field to allow null fields, but to check for
> duplicate entries I created a view called "check_duplicates" wich a form
> will be based on as soon as this function is working....  in the view I
> added all the records but made "ticket" an unique index... 
> 
> Now when I try to delete or add any records in my table (not even using
> the view) I get the error: DELETE failed because the following SET
> options have incorrect settings: 'ARITHABORT' 

In able to access an indexed view, there are seven settings that must apply. 
All the following must be on: ANSI_NULLS (P), ANSI_WARNINGS (R), 
ANSI_PADDING (C), QUOTED_IDENTIFIER (P), ARITHABORT (R), 
CONCAT_NULL_YIELDS_NULL (R). And this setting must be OFF: 
NUMERIC_ROUNDABORT (R).

The parantheses refers to where you control these settings. R is run-time. P 
means that the setting is saved with the stored procedure and C that it is 
saved by table column.

The default for these settings vary with context. With DB-Library all 
settings are OFF by default. With OLE DB and ODBC all settings are ON by 
default, save NUMERIC_ROUNDABORT and ARITHABORT. However, when you use OSQL 
or Enterprise Manager, also QUOTED_IDENTIFIER is OFF.

What happens when the indexed view cannot be used, depends on how you try to 
use it. If it is just a SELECT statement that can be resolved in other way, 
you will only get another query plan (which is probably a less good one). If 
you try to update through the view, you get an error message.


-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

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