Arithmetic overflow error converting numeric to data type numeric - Getting in one server and Not Getting in another server.
Friday, August 10, 2012 7:05 AM
In my store procedure, i am trying to fetch the data and store it in temp table. like below,
CREATE TABLE #tempConnectivity
[SUBJ_ID] [DECIMAL](7, 0) NOT NULL,
[TEL_NBR_CD] [DECIMAL](5, 0) NULL,
[CHG_CD] VARCHAR(10) NULL,
[TEL_NBR] [NVARCHAR](240) NOT NULL,
[AREA_CD] [NVARCHAR](16) NULL,
[GEO_REF_ID] [NVARCHAR](13) NULL,
[END_IND] [BIT] NULL,
[MOBL_IND] [BIT] NULL,
[ROW_GUID] [NVARCHAR](100) NULL,
AS400_TLCM_TYPE VARCHAR(100) NULL,
START_DT DATE NULL,
END_DT DATE NULL,
bid CHAR(15) NULL,
EXTN_NUMBER VARCHAR(20) NULL,
BUSINESS_ID NUMERIC(18, 0),
DECLARE @from_last_update DATETIME = '2012-08-10 07:00:48.380',
@business_id_input_flag BIT = 0,
@job_run_via VARCHAR(50) = NULL,
@now DATETIME = GETDATE();
technical_connectivity_type.gsrl_connectivity_type AS TEL_NBR_CD,
WHEN ( @job_run_via = 'manual' ) THEN 'MANUAL'
WHEN ( ( connectivity.end_date IS NULL
OR connectivity.end_date = '2099-01-01' )
AND Datediff(dd, connectivity.create_timestamp, connectivity.update_timestamp) <= 1
AND connectivity.delete_timestamp IS NULL ) THEN 'NEW'
WHEN ( connectivity.delete_timestamp IS NOT NULL ) THEN 'DELETED'
END AS CHG_CD,
Isnull(connectivity.phone_number, connectivity.other_connectivity) AS TEL_NBR,
area_code AS AREA_CD,
country.gsrl_geo_ref_id AS GEO_REF_ID,
WHEN end_date IS NOT NULL THEN 1
END AS END_IND,
WHEN technical_connectivity_type.technical_connectivity_type = 'mobile' THEN 1
END AS MOBL_IND,
connectivity.row_guid AS ROW_GUID,
technical_connectivity_type.as400_connectivity_type AS AS400_TLCM_TYPE,
connectivity.start_date AS START_DT,
connectivity.end_date AS END_DT,
business.bid AS bid,
connectivity.extension_phone_number AS EXTN_NUMBER,
LEFT JOIN country
ON connectivity.country_id = country.country_id
LEFT JOIN technical_connectivity_type
ON connectivity.technical_connectivity_type_id = technical_connectivity_type.technical_connectivity_type_id
LEFT JOIN business
ON connectivity.business_id = business.business_id
WHERE ( EXISTS(SELECT *
FROM [address] a
WHERE a.business_id = business.business_id
AND a.address_type_id IN ( 1, 2, 3 )
AND a.country_id IN ( 206, 124 )) )
AND ( business.subj_id IS NOT NULL )
AND ( ( @from_last_update IS NOT NULL
AND connectivity.update_timestamp BETWEEN @from_last_update AND @now )
OR ( @business_id_input_flag = 1
AND connectivity.business_id IN (SELECT business_id
WHERE queue_status = 'P') ) );
In one server, i am not getting any exception such as Arithmetic... But on other server, i am facing this issues.
In both the server, the fetching data types are similar,
Friday, August 10, 2012 7:28 AMIf you restore the db from the working server to the one that fails do you still get the same result?
Friday, August 10, 2012 8:03 AM
Both the servers table contains similar data types. one in UAT and another one is production. It is working fine in UAT and not in Production.
Friday, August 10, 2012 8:23 AM
This must be some data difference that you are getting two different results(Production will have different data than UAT.). You(your execution) are trying somewhere to hold big value than the variable can hold as you declared.
Can you try to change the decimal to decimal(10,2) and try.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 10:57 AMThis looks like a data issue. To confirm, you'd need to put EXACTLY the same data in the other environment and retest to rule in/out any configuration differences.
Friday, August 10, 2012 12:18 PM
Can you run the same query from Query window and post the error you getting and line number where it is encountered.
Can you try increasing BUSINESS_ID numeric(20,0) or numeric(22,0).
Hope this helps.............
Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/
- Marked As Answer by Karthick Prabhu Friday, August 10, 2012 12:34 PM
Friday, August 10, 2012 12:36 PM
Thanks a lot Guys!!! Yes it was because of data issue. Few data's length is more than the expected length.
- Edited by Karthick Prabhu Friday, August 10, 2012 12:37 PM