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 |
|
pits34
Starting Member
2 Posts |
 Posted - 2003-06-19 : 02:51:31
|
| We are trying to insert/update into a table that in turn has indexed views.We are using ODBC for SQL Server to connect and the connect string does not put an arithabort. We are getting an "arithabort/Quoted identifier(QI) option not set error". Question is instead of supplying the set arithabort and set QI on the connect string, could we provide a server wide setting.As a test i did the following but did not help. This time i purposely unchecked my query analyzer options on arithabort and QI. I tried setting arithabort via sp_dboption on the DB and on the connection setting on SQLEM server properties and update still fails with the same error.Does it mean that no matter what you configure on the server connection props or the DB options, you always need to hard code set arithabort and QI in order to avoid this error.Any help or insights.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-19 : 04:27:27
|
| From SQL Server Books Online:All connections using indexes on computed columns or indexed views must have the same settings for these seven options: 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. -----------------So, to answer your question, yes you need to hard code arithabort and QI in order to use an indexed view.Tara |
 |
|
|
pits34
Starting Member
2 Posts |
Posted - 2003-06-19 : 14:58:30
|
| Thanks for the reply however what does the connection properties under SQLEM server properties signify. If you set the arithabort setting there then what does it do. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-19 : 15:02:00
|
| Do you have to use ODBC? Any chance for OLE DB or the native ADO.NET TDS interface?Jonathan{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-19 : 15:16:21
|
| Probably what is happening is that ODBC is overriding the sp_dboption that you set. ODBC is most likely setting ARITHABORT to OFF. When you change the setting using ALTER or sp_dboption, you are just changing the default behavior, meaning it can be overwritten.Tara |
|
|
|
|
|
|
arithabort problems