SQL Server – Fixing Arithmetic Overflow Error for Data Type Smallint

sql serversql-server-2008-r2

Same query run on two identical servers of the same SQL version(including SP/KB level) with identical server and database level settings errors out on one server(Arithmetic overflow error for data type smallint, value = xxxxx.) but executes successfully on the other one.
Any ideas?

Thanks,
VJ

Query:

SELECT
       c.cost_owner_key1
       ,pl_qty =
        CASE
            WHEN pa.PnlOwnerCode='C' AND pa.PnlOwnerSubCode = 'PR' THEN
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN -1
                        WHEN 'Sell' THEN 1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
            ELSE 
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN 1
                        WHEN 'Sell' THEN -1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
        END
    ,pl_qty_factor =
        CASE 
            WHEN c.cost_qty_uom_code = i.contr_qty_uom_code THEN 1
            ELSE (a.secondary_actual_gross_qty / a.ai_est_actual_gross_qty)
        END 
    ,contr_qty_uom = c.cost_qty_uom_code
    ,alloc_num = c.cost_owner_key1
    ,alloc_item_num = c.cost_owner_key2
       ,CAST(c.cost_owner_key2 AS smallint)
FROM 
    pc_pnl_cp2_pnl_asof pa
    INNER JOIN cost c WITH(NOLOCK) ON c.cost_num = pa.PnlRecordKey 
                     AND pa.PnlOwnerCode = 'C'
    INNER JOIN ai_est_actual a WITH(NOLOCK) ON a.alloc_num=c.cost_owner_key1 
                              AND a.alloc_item_num=c.cost_owner_key2 
                              AND a.ai_est_actual_num=c.cost_owner_key3
    INNER JOIN trade_item i WITH(NOLOCK) ON i.trade_num = pa.TradeId
                           AND i.order_num = pa.OrderNumber
                           AND i.item_num = pa.ItemNumber
WHERE 
    pa.CostStatus != 'CLOSED' 
    AND pa.CostOwnerCode = 'AA' 
    AND pa.CostType = 'WPP'

Best Answer

You mentioned database and server-level settings are the same, but how sure about that are you? And is it possible something is changing a setting at the session level?

This produces the error for me (of course):

SELECT CAST(40000 AS smallint);

But this does not - it just returns NULL as the value:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SELECT CAST(40000 AS smallint);

Check to make sure the session doesn't have these SET options disabled by running this on both machines and seeing if the value is different:

SELECT @@OPTIONS;

You could also check to see if this is set at the server level using sp_configure:

EXEC sp_configure 'user options';

The more likely situation is that, due to having a different execution plan on the different servers, the higher value is being filtered out before being cast. Solutions to that are covered here (and in the linked duplicates):

CAST throwing error when run in stored procedure but not when run as raw query

Related Question