Arithmetic overflow error converting numeric to data type numeric - Getting in one server and Not Getting in another server.

Question

Hi,

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),
       delete_time_stamp DATE
    );


        
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();

INSERT #tempConnectivity
SELECT business.subj_id,
     technical_connectivity_type.gsrl_connectivity_type                 AS TEL_NBR_CD,
     CASE
       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'
       ELSE 'UPDATED'
     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,
     CASE
       WHEN end_date IS NOT NULL THEN 1
       ELSE 0
     END                                                                AS END_IND,
     CASE
       WHEN technical_connectivity_type.technical_connectivity_type = 'mobile' THEN 1
       ELSE 0
     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,
     business.business_id,
     connectivity.delete_timestamp
FROM   connectivity
     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
                                                  FROM   piccard_to_gsrl_queue
                                                  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,

Please help,

Thanks!

Answer 1

If you restore the db from the working server to the one that fails do you still get the same result?

Answer 2

Hi,

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.

Please advice,

Thanks!

Answer 3

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!

Answer 4

This 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.

Answer 5

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/

Answer 6

Thanks a lot Guys!!! Yes it was because of data issue. Few data's length is more than the expected length.

Thanks,

Karthick.