pROBLEM IN EXECUTING STORE PROCEDURE IN VB.NET2003

Question

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 31-Jul-13 21:04pm

ashu2001

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Raja Sekhar S · Answer 1 · 2013-07-31T21:13:00

Solution 1

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[^]

Posted 31-Jul-13 21:13pm

Raja Sekhar S

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 = ''
)

Raja Sekhar S 2-Aug-13 1:08am

Can you Post the Code and table Structure too.. so that we can help... Use Improve Question Widget...

_Asif_ · Answer 2 · 2013-07-31T21:31:00

Solution 2

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[^]

Posted 31-Jul-13 21:31pm

_Asif_