sql server 2005

Why am I getting this error?
I have a store procedure that it's been working, suddenly i'm getting this error and have no idea why.

Arithmetic overflow error converting expression to data type tinyint.
HNA071252Asked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
just a suggestion

add

else 1

to all your case statement... like this:

PlnIP = PlnIP/(CASE [cntContr]
WHEN 1 THEN ((Mn1 - 1) + ((13 - Mn1) * (1 + Mn1IP)))
WHEN 2 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1IP)) + ((13 - Mn2) * (1 + Mn2IP)))
WHEN 3 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1IP)) + ((Mn3 - Mn2) * (1 + Mn2IP) * (1 + Mn1IP)) +
((13 - Mn3) * (1 + Mn3IP) * (1 + Mn2IP) * (1 + Mn1IP)))
ELSE 1 END)

are you sure cntContr is always 1,2,3????
0
 
HainKurtSr. System AnalystCommented:
it says what the problem is: "Arithmetic overflow error converting expression to data type tinyint."

in your sp, you are trying to cast/convert/assign a big integer to tinyint... post the code, or find the varieble which is defined as tinyint and make it int or bigint
0
 
radcaesarCommented:
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HNA071252Author Commented:
i'm using sql server 2005.

And the code is in this block:

                  UPDATE  BsYr SET PlnUpdated = 1,
                        PlnIP = PlnIP/(CASE [cntContr]
                        WHEN 1 THEN ((Mn1 - 1) + ((13 - Mn1) * (1 + Mn1IP)))
                        WHEN 2 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1IP)) + ((13 - Mn2) * (1 + Mn2IP)))
                        WHEN 3 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1IP)) + ((Mn3 - Mn2) * (1 + Mn2IP) * (1 + Mn1IP)) +
                        ((13 - Mn3) * (1 + Mn3IP) * (1 + Mn2IP) * (1 + Mn1IP))) END),  
                        PlnOP = PlnOP/(CASE [cntContr]
                        WHEN 1 THEN ((Mn1 - 1) + ((13 - Mn1) * (1 + Mn1OP)))
                        WHEN 2 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1OP)) + ((13 - Mn2) * (1 + Mn2OP)))
                        WHEN 3 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1OP)) + ((Mn3 - Mn2) * (1 + Mn2OP) * (1 + Mn1OP)) +
                        ((13 - Mn3) * (1 + Mn3OP) * (1 + Mn2OP) * (1 + Mn1OP))) END),  
                        PlnTot = PlnTot/(CASE [cntContr]
                        WHEN 1 THEN ((Mn1 - 1) + ((13 - Mn1) * (1 + Mn1Tot)))
                        WHEN 2 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1Tot)) + ((13 - Mn2) * (1 + Mn2Tot)))
                        WHEN 3 THEN ((Mn1 - 1) + ((Mn2 - Mn1) * (1 + Mn1Tot)) + ((Mn3 - Mn2) * (1 + Mn2Tot) * (1 + Mn1Tot)) +
                        ((13 - Mn3) * (1 + Mn3Tot) * (1 + Mn2Tot) * (1 + Mn1Tot))) END)  
                  FROM TRP.FFS_Data_ProjBase BsYr INNER JOIN TRP.FFS_Data_ProjBase_Calc calc ON
                        BsYr.Parent_Id_sfx = calc.ParentTIN AND
                        BsYr.FFS_Contract = calc.TypeOfContract
0
 
8080_DiverCommented:
So, check the datatypes of the various columns involve and set up a SELECT statement that will "select" the values you are trying to update the tinyint columns too.  When you execute the SELECT statement, it should show you which of the tinyint columns you are overflowing.
0
 
HainKurtSr. System AnalystCommented:
is this working fine

UPDATE  BsYr SET PlnUpdated = 1
FROM TRP.FFS_Data_ProjBase BsYr INNER JOIN TRP.FFS_Data_ProjBase_Calc calc ON
                        BsYr.Parent_Id_sfx = calc.ParentTIN AND
                        BsYr.FFS_Contract = calc.TypeOfContract

if it is, then add other columns one by one (each set column) until you find the issue...
0
 
HNA071252Author Commented:
UPDATE  BsYr SET PlnUpdated = 1
FROM TRP.FFS_Data_ProjBase BsYr INNER JOIN TRP.FFS_Data_ProjBase_Calc calc ON
                        BsYr.Parent_Id_sfx = calc.ParentTIN AND
                        BsYr.FFS_Contract = calc.TypeOfContract

This one alone run fine. And in this table only PlnUpdated has tinyint and the rest of the columns (PlnIP, PlnOP, PlnTot) has float.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.