Arithmetic overflow error

Question

Arithmetic overflow error

Panks-913490

SSCrazy

Points: 2131
More actions
December 15, 2008 at 9:30 am

#197391

I am working on SQL 2000 to 2005 migration project.
One of my proc uses the below script:
SET ARITHABORT OFF
UPDATE Prubond_MVR_Calc
SET MVRpct = (1- SFSurVal * (1 + Leeway) / TBSurVal)
FROM Prubond_MVR_Calc WHERE TBSurVal <>0
Now this runs fine in an SQL 2000 environment but in SQL 2005 it fails with 'arithmetic overflow error'.
The thing is i want it to work exactly the way it works in SQL 2000.
That is, this statement gets terminated WITHOUT throwing any error.
In SQL 2005 this statement is failing my proc. i have tried transaction rollback but that too is not working.
Any help would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Panks (12/15/2008)
I am working on SQL 2000 to 2005 migration project.
One of my proc uses the below script:
SET ARITHABORT OFF
UPDATE Prubond_MVR_Calc
SET MVRpct = (1- SFSurVal * (1 + Leeway) / TBSurVal)
FROM Prubond_MVR_Calc WHERE TBSurVal <>0
Now this runs fine in an SQL 2000 environment but in SQL 2005 it fails with 'arithmetic overflow error'.
The thing is i want it to work exactly the way it works in SQL 2000.
That is, this statement gets terminated WITHOUT throwing any error.
In SQL 2005 this statement is failing my proc. i have tried transaction rollback but that too is not working.
Any help would be greatly appreciated.

The following three settings work together:

SET ARITHABORT

SET ARITHIGNORE

SET ANSI WARNINGS

What are the default settings in both environments?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Panks-913490 SSCrazy Points: 2131 More actions · Answer 2

Resolved...

Used a simple try catch block...

Thanks a lot anyways...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Panks (12/15/2008)
Resolved...
Used a simple try catch block...
Thanks a lot anyways...

Which setting was it, Panks?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden