Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chrispy
Posting Yak Master
107 Posts |
 Posted - 2002-03-20 : 16:44:16
|
| Hello, I am working on setting up indexes on a new MS SQL DB. I have been using the MS index wizard as well as creating some mannually. Problem it that I end up getting an error whe we try to insert after the index is created.[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.Not sure what is going wrong but Books Online was not to helpful in this area. If it matters I am letting the Wizard create views as well.Chris |
|
|
Jay99
468 Posts |
Posted - 2002-03-20 : 17:03:54
|
I don't have much experience with the index wizard, but I do know that ...quote:
The connection option settings required for indexed views and indexes on computed columns must be active: - For any connection that creates an index on a view or computed column.
- For any INSERT, UPDATE, or DELETE statements that attempt to modify data covered by an index on a view or computed column.
- Before the optimizer can consider using an index on a view or computed column to cover a query.
- For indexed views, the ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON when the view is created, because these two settings are stored as object properties with the view definition.
I am betting you are on SQL 2K and the index wizard has created either a materialized view or an index on a computed column, but your connection setting don't meet the requirements for running dml against the objects . . . look up indexed views in bol and look for the 6 set options that must be on and the 1 that must be off . . .Until I got things squared away, I had a lot of trouble with this at my last contract.Jay<O> |
 |
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-03-20 : 18:13:08
|
| Jay, Thanks a million once again. I found what I was looking for in BOL. I just did not know what to search on. Your assumptions were correct except for the computed column. MS wizard did create the view. The SQL statement stayed the same. I do no that (in this case) the index is the data just sorted a differnt way to opt the lookups against data with those indexes on them . How does writing back create a problem than? Is the Original SQL statement not writing back to the original table? what am I missing??For the Record:These six SET options must be set to ON: ANSI_NULLSANSI_PADDINGANSI_WARNINGSARITHABORTCONCAT_NULL_YIELDS_NULLQUOTED_IDENTIFIER The NUMERIC_ROUNDABORT option must be set to OFF. Thanks again,ChrisBTW that 300+ second query is down to .3 sec |
|
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-03-21 : 10:57:09
|
| ALl, If I create a View and than later write to the table that I create the view on I get this error. I have found what I need to do in order to get the inserts correctly, but does that mean that I have to do it for all inserts to the table that I created the view on? Or am I creating the view wrong?????Does this seem correct that if I create a view, I have to turn/off six settings every time I want to write to the table that the view was on??Thanks in advance,Chris |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 11:46:04
|
| Here is the part that is kinda voodoo . . .When you run your CREATE(or ALTER) PROC, whatever the SET setting are, at that time, for that connection, will override the clients setting during subsequent executions of the proc. In other words, compile the proc with the 6 on/1 off and then from on you will not have to worry about it.I love materialized views. I think the song & dance around this issue sucks . . .Jay<O> |
|
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-03-21 : 14:10:57
|
| Jay, Thanks again. I sort of knew that was coming. :) Ya see they are not stored procedures. As I am forcing them to convert this is another reason why.Thanks,Chris |
|
|
|
|
|
|
|
|
Newbie Index problem/ question