1

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'
0
3

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.