tsql arithmetic overflow converting expression to data type int only with adodb

Question

with the application I am working on I get this error:

error '80040e57'
Arithmetic overflow error converting expression to data type int

when this query:

    SELECT 0 as type_elm
, 999 AS key0
, def_car.desc_elm as sh_elm
, def_car.desc_elm
, id_prd = CAST((CASE WHEN def_key0.key0 IS NULL AND def_car.desc_elm <> 'Non Perfezionati'
 THEN CAST((-100 * def_ger.id_prd) as bigint) ELSE (-100 * def_ger.id_cld) + (ISNULL(def_key0.key0,9) * 10) END) AS BIGINT)
, id_cld = CAST((CASE WHEN def_ger.id_cld > 0 THEN CAST((-100 * def_ger.id_cld) as bigint) ELSE 0 END ) AS BIGINT)
 ,CAST(0 AS BIGINT) as id_prd_real
 ,CAST(0 AS BIGINT) as cessato_area
, cessato = CAST((CASE WHEN def_ger.id_cld = 0 THEN def_ger.id_prd ELSE 100 - (def_key0.key0 * 10) END) AS BIGINT)
 ,cessato_orig = CAST((CASE WHEN def_ger.id_cld = 0 THEN def_ger.id_prd ELSE 0 END) AS BIGINT) FROM def_car
 INNER JOIN def_ger ON def_car.id_prd = def_ger.id_prd
 LEFT JOIN def_key0 ON def_key0.desc_key0 = def_car.desc_elm
 WHERE (def_ger.key0 = 999)
 AND def_ger.key_ndg = 539
 AND (def_ger.id_cld = 0 OR def_car.id_prd > 890000000)
 AND def_car.desc_elm <> 'Cestino'
 UNION SELECT def_tree.type_elm
, 999 as key0
, def_tree.sh_elm
, def_tree.desc_elm
, id_prd = CAST((CASE WHEN def_tree.id_prd > 800000000 THEN def_tree.id_prd
 ELSE CAST(CAST(def_car.id_prd as varchar) + RIGHT(('0000' + CAST(def_tree.id_prd as varchar)), 4) as bigint ) END) AS BIGINT)
, id_cld = CAST((CASE WHEN def_tree.id_cld > 800000000 THEN def_tree.id_cld WHEN def_tree.id_cld <= 0 THEN (-100 * def_ger.id_cld) + (def_tree.key0 * 10)
 ELSE CAST(CAST(def_car.id_prd as varchar) + RIGHT(('0000' + CAST(def_tree.id_cld as varchar)), 4) as bigint ) END ) AS BIGINT)
, id_prd_real = CAST((CASE WHEN def_tree.id_prd > 0 THEN def_tree.id_prd ELSE def_car.id_prd END ) AS BIGINT)
 ,CAST(def_car.id_prd AS BIGINT) as cessato_area
, CAST(0 AS BIGINT) as cessato
, CAST(0 AS BIGINT) as cessato_orig FROM def_tree LEFT JOIN def_key0 ON def_tree.key0 = def_key0.key0 LEFT JOIN def_car ON def_key0.desc_key0 = def_car.desc_elm
 LEFT JOIN def_ger ON def_ger.id_prd = def_car.id_prd WHERE def_ger.key_ndg = 539 ORDER BY cessato DESC
, id_cld
, type_elm DESC
, desc_elm

is executed in an asp Application with an adoDb object, the wierd think is that this query is executed without problems in SQL Server and after executed into SQL Server the application runs without erro until I clear the cache of SQL Server.I do not understand this issue.

possible duplicate of How get around the arithmetic overflow error converting expression to data type int? — user692942
– user692942, Commented Aug 17, 2015 at 11:52

Dan Guzman · Accepted Answer · 2015-08-17 10:46:30Z

1

The reason you get the error from ADO but not SSMS is different execution plans. One plan likely evaluates the expression with the problem value earlier in the plan.

Consider changing expressions like this from:

CAST((-100 * def_ger.id_cld) as bigint)

to

CAST(-100 * CAST(def_ger.id_cld as bigint))

so that the intermediate result as well as the final result is the larger type.

answered Aug 17, 2015 at 10:46

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

tsql arithmetic overflow converting expression to data type int only with adodb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related