Arithmetic overflow, identifying what columns and rows?

Question

I have a humongous view of some 236 columns, containing atleast 500'000 rows. If I do a simple SELECT * FROM VIEW, I get a arithmetic overflow at some point, and the SELECT aborts.

I know exactly why- in some row and column, the CAST(COL AS DECIMAL(x,y)) fails, which is caused by a number which is too big to fit the restrictions posed by the casting. I need to identify that exact row and column, and I'm looking to find a automated way to find the data that causes this.

Is this possible somehow to do in some automated way? As the brute force way, is a bit too exhaustive, as I have to go through the relevant columns (about 80 columns are being casted to decimal), and afterwards finding the exact row, for which I haven't developed a method for yet. I can't "see" the row, as it fails, thus never displays.

The view is on SQL Server 2008 R2.

Community · Accepted Answer · 2017-05-23 12:06:33Z

2

I found a few answers on SO that might be helpful.

How to figure out which column raises an arithmetic overflow error upon insert? (has a script to determine the column)

SELECT  'PRINT ''' 
        + sc.Name 
        + '''; SELECT MIN(CAST(' 
        + sc.Name 
        + ' AS INTEGER)) FROM Usertable'
FROM    sys.columns sc 
        INNER JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE   OBJECT_NAME(Object_ID) = 'BaseTable'
        AND st.name = 'INT'

Once you know the column you should be able to drill-down to find the row. There is also another SO question regarding finding the row:

Find out which row caused the error

Or this question:

T-SQL Arithmetic overflow: which column?

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Mar 20, 2012 at 17:27

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

wickedone · Accepted Answer · 2012-03-21 00:39:19Z

0

Yeah, unfortunately there is not a good automated way to find the value that is invalid for a given cast. You're pretty much stuck doing divide and conquer and writing queries such as the one referenced by bluefeet to narrow it down.

The technique of using the DMVs as in that query will save you some time.

Also, beware of statements such as

select cast(col as decimal(18,6)
where isnumeric(col)

Not only does isnumeric handle certain values unexpectedly, but sql server is free to determine the order to apply the operations, so that query may end up applying the cast before filtering the rows. This can happen even when the filter is inside of a subquery or CTE.

Finally, remember that you can use the top x percent clause in your investigation to help you narrow down the rows causing problems.

edited Mar 21, 2012 at 0:39

wickedone

5401 gold badge6 silver badges18 bronze badges

answered Mar 20, 2012 at 18:37

Alex Weitzer

1811 silver badge12 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow, identifying what columns and rows?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related