Monday, August 25, 2008 3:08 PM
Hi all... Have a look at this command.... it works in SQL Server 2005 but fails in 2008 RTM:
Declare@num_Passed Numeric(2, 0);
Declare@num_CheckoutPositionID Numeric(3, 0);
Set@num_Passed = -1;
Set@num_CheckoutPositionID = 3;
IF(@num_CheckoutPositionID = 3) AND (@num_Passed = 0) BEGIN
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.
Now, I found 2 fixes:
1. I changed the numeric data types to integer. (Which is what it should be anyways)
2. Change statememt to: IF (@num_CheckoutPositionID = 3.0) AND (@num_Passed = 0.0)
I found this problem in some SPs which were at least 8 years old. I will admit the datatype should have originally been integer..... but still, it should work.
My point is... be careful before upgrading your SQL Instance!
Can someone else check to see if they get this same error? I want to make sure it isn't my installation.
Monday, August 25, 2008 3:18 PMModerator
BTW the problem only occurs when you use negative values
This is because numeric(2,0) is implicitly converted to tinyint, tinyint can not store negative values
this worksCode Snippet
IF (convert(Numeric(2, 0),1) = 0)
this doesn'tCode Snippet
IF (convert(Numeric(2, 0),-1) = 0)
Using numeric(3,0) is no problem eitherCode Snippet
IF (convert(Numeric(3, 0),-1) = 0)
This is very interesting because this worksCode Snippet
Declare @num_Passed Numeric(2, 0);
Set @num_Passed = -1;
Denis The SQL Menace
Monday, August 25, 2008 3:26 PMFascinating.... I'm wondering why it works in 2005....
Monday, August 25, 2008 3:30 PMModerator
I was able to reproduce the error. The script executed ok on SS 2005 DE, but not in SS 2008 DE RTM.
I posted it in the private SQL Server MVP newsgroup and will update here if I get any reponse.
Monday, August 25, 2008 3:39 PMModerator
The question here is why are they converting it to tinyint in the logical expression?
Monday, August 25, 2008 3:45 PMModerator
My guess is optimization of some sort, which is interesting because the following is just a plain vanilla int
cast(sql_variant_property(1,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(1,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(1,'Scale') as varchar(10)) + ')'
Let's see what comes back out of the newsgroup
Denis The SQL Menace
Monday, August 25, 2008 4:02 PMModerator
They are breaking their own rules, because numeric/decimal data type has greater precedence than tinyint, so the number in the right of the expression should have been converted to numeric/decimal in order to do the comparison, and not the contrary, from numeric/decimal to tinyint.
Monday, August 25, 2008 4:05 PMModerator
This is defenitely a bug. Could you file this bug on microsoft connect, please?
You will need to login using a Windows Live ID account.
Monday, August 25, 2008 4:30 PM
Tuesday, August 26, 2008 1:43 PM
To everyone: Please verify our findings and add your validation to the M/S Connect site. Hopefully this will speed up the "Fix" process.