I am a Singapore based ASP/VB developer and need your advise for a problem i am facing in my VB/SQLServer/Stored Procedure application.
When i executed a stored procedure(SQLServer 6.5) from VB(6.0) application, I had error messages, from ADO error collection, which are "Arithmetic overflow error converting numeric to data type numeric." followed by "The statement has been terminated.". This is the exact message(s) i am getting.
The commands i executed are :
stock_value = convert(numeric(9,3), convert(numeric(28,3) , "2593500")/1 ),
day = convert(Char(3), datename(dw, getdate())) ,
stock_timestamp = getdate()
stock_code="C27 " and
Pls note that the database data type of "stock_value" column is "int".
stock_volume = convert(numeric(9,3), convert(numeric(28,3) , "1017000")/1 ),
day = convert(Char(3), datename(dw, getdate())) , stock_timestamp = getdate()
stock_code="C27 " and
Pls note that the database data type of "stock_volume" column is "numeric](11, 2)".
Need you advise/help as this is taking up lot of my time.
Originally posted by r937
okay, try not putting quotes around your numerics
e.g. set stock_value = 2593500 / 2
2593500 is also a parameter of string and is being passed from VB. My backend VB application is updating the database only and this program is an ugraded version of existing version. Currently, there are ASP pages accessing this data from the sqlserver database.
I can do the conversion on the VB side. But my concern is that it may affect the formatting for existing ASP pages. And also that, this will lead to changes in the parameter datatype.
For this reason, i am keeping all the existing data types for the parameters for all the functions and stored procedures.
Here is the problem - you have a value that has 7 digits to the left and 3 digits to the right of the decimal point. However, your precision that you are asking for is 9 digits total - but you have 10 total. That is why you are receiving the overflow. Either change to (9,2) or (10,3) and that will fix your overflow problem. Also, be careful and examine how sql server handles rounding/truncating when you store an int/decimal(11,2) from a decimal(x,3). In your case -- When you convert to an int, ss will truncate. When you convert to a smaller decimal place, ss will round. So, in your situation you have 2 different "rounding" models which may eventually affect (adversely) your results - depending on how scientific or precise you are trying to be.
rnealejr is correct, (you are receiving the overflow because you have 10 digits that you are casting to 9). I would add the following more general warning:
It is often very important to be aware of the implications of (both) explicit (as rnealejr pointed out) and implicit type conversions (unfortunatly, the results are often DBMS implementation, and / or version specific);
-- Simple example:
1234567 / 17 As 'Truncation',
1.0000 * (1234567 / 17 ) As 'Truncation + Scale',
1.0000 * 1234567 / 17 As 'LessTruncation',
1.0000 * 1234567 / '17' As 'MuchLessTruncation',
1.0000 * (1234567 / '17') As 'Truncation + Scale'
-- More simple examples with some explanation:
1234567.0000 / 17.0000 As 'A',
(1.0000 * 1234567) / (1.0000 * 17) As 'B',
1234567 / 17 As 'C'
-- Some Conversions involve truncation
Cast ((1234567.0000 / 17.0000) AS Int) As 'Cast A --> Int',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Int) As 'Cast B --> Int',
Cast ((1234567 / 17) AS Int) As 'Cast C --> Int'
-- Some Conversions involve rounding
Cast ((1234567.0000 / 17.0000) AS Money) As 'Cast A --> Money',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Money) As 'Cast B --> Money',
Cast ((1234567 / 17) AS Money) As 'Cast C --> Money'
-- Other Conversions are not as intuitively obvious
-- Char (implicit conversions) of A, B, and C give:
Select 1234567.0000 / '17.0000' As 'A Char',
'B Char results in an overflow' As 'B Char',
1234567 / '17' As 'C Char'
-- NOTE B Char --> results in an overflow (OVERFLOW error result)
Select (1.0000 * 1234567) / (1.0000 * '17') As 'B Char Overflow'