Click here to Skip to main content
15,566,152 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All, I have used a computed field in a table and that table is used in my store procedure now when i make execute that procedure from sql server client it works fine and WHEN i try to used that store procedure in vb.net code then it throws the following error :- "UPDATE 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."

Please Help i tried "SET ARITHABORT on" in the beginning of the store procedure but nothing positive happened.
Posted

Quote:
Terminates a query when an overflow or divide-by-zero error occurs during query execution.

If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation.
Note If neither SET ARITHABORT nor SET ARITHIGNORE is set, Microsoft® SQL Server™ returns NULL and returns a warning message after the query is executed.
When an INSERT, DELETE or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation when SET ARITHABORT is OFF, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.


Link: ARITHABORT - MSDN[^]
 
Share this answer
 
Comments
ashu2001 1-Aug-13 7:27am    
will using Non Persisted Computed column save me from this problem???
Raja Sekhar S 1-Aug-13 7:40am    
Is there any Divide by Zero Error Occuring..? if that is the Case u Can Use SET ARITHABORT OFF
ashu2001 2-Aug-13 0:31am    
@Raja. I am getting "Warning: Null value is eliminated by an aggregate or other SET operation" but not able to identify at which location i am getting this warning, wat i think i that i am getting this warrning when i try to Insert/update any selective information in the computed table.
Raja Sekhar S 2-Aug-13 1:01am    
If SET ARITHABORT OFF Then Null Value is Assigned to the Column When Divide by Zero Error Occur.. so if u are Using Aggregate Function on a column where Divide by Zero Error Occurs.. this Warning will come...
ashu2001 2-Aug-13 0:35am    
When i se ARITHABORT OFF in the procedure it gives me

"DELETE 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."

i got the above error at below location

DELETE FROM ABC Table(A physical table with calculated Field with Persistance)
WHERE ( Host = HOST_NAME()
OR Host = ''
)
The problem is in your computed column. The computation is failing with divide by zero error or overthrow. Check this link

http://msdn.microsoft.com/en-us/library/ms190306.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900