Wednesday, June 25, 2008

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

SYMPTOMSWhen 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.

CAUSETo 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.

MY SOLUTIONMy own solution was to rebuild stored procedures and indexed used with the table you are updating.

RESOLUTIONTo 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.