Error: Arithmetic overflow error converting numeric to data type numeric

Hello,
I'm using adonet to update the database hundreds of fields all at once and
got this exception/error:
"Arithmetic overflow error converting numeric to data type numeric", but it
doesn't tell me which specific column got overflown. Is there a way to find
out ? I have access to both the update code and the sql server.
Thank you very much in advance...Although I am not much of an ADO.Net person, I think that you get an errors
collection... SQL does return more info... Either it is in one of the other
messages or the one you are seeing is truncated... I did an arithmetic
overflow and got the following message..
Server: Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 3500.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23Whl0SBJEHA.2440@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using adonet to update the database hundreds of fields all at once and
> got this exception/error:
> "Arithmetic overflow error converting numeric to data type numeric", but
it
> doesn't tell me which specific column got overflown. Is there a way to
find
> out ? I have access to both the update code and the sql server.
> Thank you very much in advance...
>|||hii
change the size of all your numeric fields in th data base to 38 and
precison to 0 and see..
ramesh|||If I don't error the error after that, how would I know which one?
"Ramesh" <ramesh@.mail.punecity.com> wrote in message
news:uJe8DkDKEHA.268@.TK2MSFTNGP11.phx.gbl...
> hii
> change the size of all your numeric fields in th data base to 38 and
> precison to 0 and see..
> ramesh
>sql

Error: Arithmetic overflow error converting numeric to data type numeric

Hello,
I'm using adonet to update the database hundreds of fields all at once and
got this exception/error:
"Arithmetic overflow error converting numeric to data type numeric", but it
doesn't tell me which specific column got overflown. Is there a way to find
out ? I have access to both the update code and the sql server.
Thank you very much in advance...Although I am not much of an ADO.Net person, I think that you get an errors
collection... SQL does return more info... Either it is in one of the other
messages or the one you are seeing is truncated... I did an arithmetic
overflow and got the following message..
Server: Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 3500.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23Whl0SBJEHA.2440@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using adonet to update the database hundreds of fields all at once and
> got this exception/error:
> "Arithmetic overflow error converting numeric to data type numeric", but
it
> doesn't tell me which specific column got overflown. Is there a way to
find
> out ? I have access to both the update code and the sql server.
> Thank you very much in advance...
>|||hii
change the size of all your numeric fields in th data base to 38 and
precison to 0 and see..
ramesh|||If I don't error the error after that, how would I know which one?
"Ramesh" <ramesh@.mail.punecity.com> wrote in message
news:uJe8DkDKEHA.268@.TK2MSFTNGP11.phx.gbl...
> hii
> change the size of all your numeric fields in th data base to 38 and
> precison to 0 and see..
> ramesh
>

Error: Arithmetic overflow error converting numeric to data type numeric

Hello,
I'm using adonet to update the database hundreds of fields all at once and
got this exception/error:
"Arithmetic overflow error converting numeric to data type numeric", but it
doesn't tell me which specific column got overflown. Is there a way to find
out ? I have access to both the update code and the sql server.
Thank you very much in advance...
Although I am not much of an ADO.Net person, I think that you get an errors
collection... SQL does return more info... Either it is in one of the other
messages or the one you are seeing is truncated... I did an arithmetic
overflow and got the following message..
Server: Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 3500.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23Whl0SBJEHA.2440@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using adonet to update the database hundreds of fields all at once and
> got this exception/error:
> "Arithmetic overflow error converting numeric to data type numeric", but
it
> doesn't tell me which specific column got overflown. Is there a way to
find
> out ? I have access to both the update code and the sql server.
> Thank you very much in advance...
>
|||hii
change the size of all your numeric fields in th data base to 38 and
precison to 0 and see..
ramesh
|||If I don't error the error after that, how would I know which one?
"Ramesh" <ramesh@.mail.punecity.com> wrote in message
news:uJe8DkDKEHA.268@.TK2MSFTNGP11.phx.gbl...
> hii
> change the size of all your numeric fields in th data base to 38 and
> precison to 0 and see..
> ramesh
>

Error with BigDecimal used as stored procedure parameter

I have the following stored procedure:
create procedure MyNumericTestProc
(
@.param1 numeric(13,2) output
)
as
begin
if (@.param1 is NULL)
begin
set @.param1 = 5.25
end
set @.param1 = @.param1 + 0.01
select @.param1
end
I call it using the MS SQL Server JDBC Driver (SP3):
public class TestMyNumericTestProc
{
public static void main(String[] args)
{
try
{
testMyNumericTestProcUsingJDBC();
}
catch (ClassNotFoundexception e)
{
}
}
public void testMyNumericTestProcUsingJDBC() throws
ClassNotFoundException
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://MySystem\\MySQL2000Server:MyPort;databaseName=My
Database",
"username",
"password");
cs = conn.prepareCall("{call MyNumericTestProc(?)}");
BigDecimal d = new BigDecimal("300.10");
int scale = 3;
System.out.println("Input Value = " + d.toString());
System.out.println("Input Value Scale = " + d.scale());
System.out.println("Input Parameter Scale = " + scale);
// Set a BigDecimal inout parameter and execute call
cs.setObject(1, d, Types.DECIMAL);
cs.registerOutParameter(1, Types.DECIMAL, scale);
boolean csResult = cs.execute();
// Obtain result set
rs = cs.getResultSet();
rs.next();
d = rs.getBigDecimal(1);
System.out.println("ResultSet Value = " + d.toString());
System.out.println("ResultSet Scale = " + d.scale());
// Obtain value of the output parameter as object
Object obj = cs.getObject(1);
System.out.println("Output Param Value (as Object) = " +
((BigDecimal) obj).toString());
System.out.println("Output Param Scale (as Object) = " +
((BigDecimal) obj).scale());
// Obtain value of the output parameter as BigDeciaml
d = cs.getBigDecimal(1);
System.out.println("Output Param Value (as BigDecimal) = " +
d.toString());
System.out.println("Output Param Scale (as BigDecimal) = " +
d.scale());
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cs != null) {
try { cs.close(); }
catch (SQLException e) {;}
}
if (conn != null) {
try { conn.close(); }
catch (SQLException e) {;}
}
}
}
}
The output of executing this class is as follows:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 30.02
ResultSet Scale = 2
Output Param Value (as Object) = 30.020
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 30.020
Output Param Scale (as BigDecimal) = 3
Note that I use BigDecimal as the parameter type (which is the recommended
type fr DECIMAL and NUMERIC).
Given the stored procedure, I would have expected the value 300.11 as the
value of the output
parameter and within the result set.
It appears there is an error when the scale of the input value and the scale
specified by the output
parameter do not match, and the input parameter is a BigDecimal.
Is this a problem within the realm of the MS JDBC Driver? If it is not how
do I determine where the
error is occuring?
Try running your code against another driver. If it works, then it's
probably a MS JDBC Driver problem. And it will work.
Alin.
|||I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
what one would expect:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 300.11
ResultSet Scale = 2
Output Param Value (as Object) = 300.110
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 300.110
Output Param Scale (as BigDecimal) = 3
Now that I have determined this is a problem in the SQL Server JDBC Driver,
where do I file an error/bug report so that Microsoft is aware of the issue
(and possibly an idea of when the problem may be fixed)?
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
> Try running your code against another driver. If it works, then it's
> probably a MS JDBC Driver problem. And it will work.
> Alin.
>
|||Fred Foozle wrote:
> Now that I have determined this is a problem in the SQL Server JDBC
Driver,
> where do I file an error/bug report so that Microsoft is aware of the
issue
> (and possibly an idea of when the problem may be fixed)?
Microsoft engineers read this newsgroup, so they should be able to
either direct you to such a place or create a bug report themselves.
But I wouldn't wait for the bug to be fixed; MS only releases a new
JDBC driver version with a new SP and they usually fix a very limited
number of bugs; check the changelogs of their previous versions to see
what I mean.
Alin.
|||Hello Fred,
I have been able to reproduce the issue as reported. I filed a bug on it
and forwarded it to development.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| Reply-To: "Fred Foozle" <ffoozle@.hotmail.com>
| From: "Fred Foozle" <ffoozle@.hotmail.com>
| Subject: Re: Error with BigDecimal used as stored procedure parameter
| Date: Thu, 7 Apr 2005 14:32:46 -0400
|
| I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
| what one would expect:
|
| Input Value = 300.10
| Input Value Scale = 2
| Input Parameter Scale = 3
| ResultSet Value = 300.11
| ResultSet Scale = 2
| Output Param Value (as Object) = 300.110
| Output Param Scale (as Object) = 3
| Output Param Value (as BigDecimal) = 300.110
| Output Param Scale (as BigDecimal) = 3
|
|
| Now that I have determined this is a problem in the SQL Server JDBC
Driver,
| where do I file an error/bug report so that Microsoft is aware of the
issue
| (and possibly an idea of when the problem may be fixed)?
|
|
|
| "Alin Sinpalean" <alin@.earthling.net> wrote in message
| news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
| > Try running your code against another driver. If it works, then it's
| > probably a MS JDBC Driver problem. And it will work.
| >
| > Alin.
| >
|
|
|