SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Recently one of my colleagues has experienced this SQL Server error while working on a BULK INSERT process t-sql query.
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
A short research on the problem showed that this SQL Server problem occurs on databases especially whose database compability is 80 which means compatible to MS SQL Server 2000 databases.
Actually the sql database the sql error occured was a Sql Server database which was upgraded from a MS SQL Server 2000 server to a MS SQL Server 2005 server, but the compability of the database was kept 80 in order assure that the applications will not crash because of this upgrade.
Actually the sql engine warning was self explaining that the ARITHABORT option was not correct for the executing task.
The SET ARITHABORT t-sql command controls the termination of a sql query being executed on cases when an overflow or divide-by-zero error occurs.
SET ARITHABORT syntax is as follows :
SET ARITHABORT { ON | OFF }
If the Set ArithAbort option is set to ON and when an overflow error or a division by zero error occurs, the executing t-sql query or the sql script or the sql batch is terminated. And the transaction is rolled back.
On the other hand, if the Set ArithAbort option is set to OFF, the sql engine will throw a warning and continue executing the sql statements by setting NULL values to the error causing arithmetic operations.