Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » 'Arithmetic overflow error converting numeric...
Add to briefcase
17 posts, Page 1 of 2
12»»

'Arithmetic overflow error converting numeric to data type numeric' Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ananda.murugesan
Posted Monday, September 17, 2012 1:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 820, Visits: 2,399
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
Post #1360009
Robin Sasson
Posted Monday, September 17, 2012 2:02 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:17 AM
Points: 674, Visits: 472
Might be useful to see some data!

"Be brave. Take risks. Nothing can substitute experience."
Post #1360015
ChrisM@Work
Posted Monday, September 17, 2012 2:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 5,606, Visits: 10,965
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1360026
ananda.murugesan
Posted Monday, September 17, 2012 3:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 820, Visits: 2,399
please find the attached sample data for header and detail table
Post #1360035
Jason-299789
Posted Monday, September 17, 2012 3:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123
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
Post #1360039
ChrisM@Work
Posted Monday, September 17, 2012 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 5,606, Visits: 10,965
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1360044
Robin Sasson
Posted Monday, September 17, 2012 3:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:17 AM
Points: 674, Visits: 472
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.

"Be brave. Take risks. Nothing can substitute experience."
Post #1360051
ananda.murugesan
Posted Monday, September 17, 2012 3:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 820, Visits: 2,399
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'
Post #1360052
Jason-299789
Posted Monday, September 17, 2012 3:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123
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
Post #1360054
ChrisM@Work
Posted Monday, September 17, 2012 3:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 5,606, Visits: 10,965
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1360058
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.