'Arithmetic overflow error converting numeric to data type numeric'

Question

Post reply

'Arithmetic overflow error converting numeric to data type numeric'

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 17, 2012 at 1:32 am

#266759

This procedure working fine in SSMS with multiple execution.
Problem is application side(c#), first time executed working fine and inserted data into table.
Second time execution throwing error is -'Arithmetic overflow error converting numeric to data type numeric' . please suggestion me how to fix this error in application side.
Header table description

Viewing 15 posts - 1 through 15 (of 16 total)

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

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

Might be useful to see some data!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

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

Please post the following:

1. Code for function dbo.GetDataType

2. Values for;

@Table varchar(50),

@FldNames varchar(8000),

@FldValues varchar(8000)

which cause the stored procedure to fail.

3. DDL for the @table in 2. above.

^{“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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

please find the attached sample data for header and detail table

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

I'm guessing that the columns/values have some how manged to get out of sync or theres a rogue value that is larger than the scope of the associated columns Numeric Value, eg Tran_CODE being put into Rec_No or some other combination.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

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

ananda.murugesan (9/17/2012)
please find the attached sample data for header and detail table
...

I don't want to open an attachment, which isn't the best way to provide the information I asked for: a set of parameters which causes the fault. This would be a table name, a set of column names, and a set of values. Also the DDL of the table.

If you wish to supply sample data, please follow the established recommendations: DDL with a set of INSERT statements.

^{“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

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 6

ananda.murugesan (9/17/2012)
please find the attached sample data for header and detail table
CREATE FUNCTION [dbo].[GetDataType] ( @Table varchar(50), @fld varchar(50) )
RETURNS varchar(1)
AS
BEGIN
Declare @datatype varchar(1)
select @datatype = case when xtype in ( 35, 175, 167 ) then 'C'
when xtype in ( 61 ) then 'D'
when xtype in ( 108, 62, 56, 52, 48 ) then 'N'
else 'X' end
from syscolumns
where id = object_id(@Table)
and name = @fld
if @@rowcount = 0
select @datatype = 'X'
Return @datatype
END
thanks

No attachments please. Provide some INSERT statements.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 7

DDL statements...

exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'

exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'

exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'

exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'

exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'

Is this the data that is causing the problem? Or does it run ok?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

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

ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'

This isn't DDL. Which of these statements cause the error? All of them?

Can you please post the DDL for 'SIL_TRAN_H' and 'SIL_TRAN_D'.

^{“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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

ChrisM@Work (9/17/2012)
ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'
This isn't DDL. Which of these statements cause the error? All of them?
Can you please post the DDL for 'SIL_TRAN_H' and 'SIL_TRAN_D'.

Chris, I think you might find the DDL for these two tables is in the Opening Post.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

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

Jason-299789 (9/17/2012)
ChrisM@Work (9/17/2012)
ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'
This isn't DDL. Which of these statements cause the error? All of them?
Can you please post the DDL for 'SIL_TRAN_H' and 'SIL_TRAN_D'.
Chris, I think you might find the DDL for these two tables is in the Opening Post.

Thanks Jason 🙂

We're still missing scripts for functions dbo.SplitString and dbo.GetPrimaryKey, and data scripts for the two tables.

^{“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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 12

This data and execute statement working fine in SSMS.

whenever call application side working fine at first time, calling next time error throwing like "'Arithmetic overflow error converting numeric... "

Insert statement will be generated whenever execute this SP..

thanks

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

ananda.murugesan (9/17/2012)
This data and execute statement working fine in SSMS.
whenever call application side working fine at first time, calling next time error throwing like "'Arithmetic overflow error converting numeric... "
Insert statement will be generated whenever execute this SP..
thanks

We need the script for the function dbo.GetPrimaryKey.

^{“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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 14

ananda.murugesan (9/17/2012)
This data and execute statement working fine in SSMS.
whenever call application side working fine at first time, calling next time error throwing like "'Arithmetic overflow error converting numeric... "
Insert statement will be generated whenever execute this SP..
thanks

Have you tried running an SQL trace to see the statements executing and then looked at the results on the SQL that is causing the problem?

_________________________________________________________________________
SSC Guide to Posting and Best Practices