PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View

Article translations Article translations
Article ID: 305333 - View products that this article applies to.
This article was previously published under Q305333
Expand all | Collapse all

SYMPTOMS

When you run a stored procedure or SQL INSERT statement directly, which attempts to insert a row into a table that is referenced in an indexed view, the following error may occur:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'
Furthermore, this error may occur even if "SET ARITHABORT ON" is included in the batch or stored procedure that attempts the INSERT.

CAUSE

To successfully insert a row into a table that is referenced in an indexed view, the SQL ARITHABORT configuration setting must be set to ON. Furthermore, the statement that applies this configuration setting must be executed in its own batch. Because stored procedures contain only one batch, adding the statement to the procedure does not work.

RESOLUTION

To resolve this problem, add the following ADO code to your application after you open the connection to your database:
MyConnection.Execute "SET ARITHABORT ON"
				
where MyConnection is a reference to the ADO connection object you are using to run the stored procedure that performs an INSERT or the SQL INSERT statement.

STATUS

This behavior is by design.

MORE INFORMATION

SET ARITHABORT ON is one of the options that is not automatically set for connections that use the OLE DB Provider for SQL Server or the SQL Server ODBC driver. Because OLE DB and ODBC connections do not specify an ARITHABORT setting, connections default to the server default, which is ARITHABORT OFF.

REFERENCES

For more information, see the "Designing an Indexed View" and "Creating an Index" topics in SQL Server 2000 Books Online.

Properties

Article ID: 305333 - Last Review: May 8, 2003 - Revision: 2.2
APPLIES TO
  • Microsoft ActiveX Data Objects 2.6, when used with:
    • Microsoft SQL Server 2000 Standard Edition
  • Microsoft ActiveX Data Objects 2.7, when used with:
    • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB305333

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com