CODEWITHSUNDEEP
sqlviewcastingoverflowdecimal
sturk
sturk

Reputation: 11

Arithmetic overflow, identifying what columns and rows?

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.

Upvotes: 1

Views: 4221

Answers (2)

Alex Weitzer
Alex Weitzer

Reputation: 181

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.

Upvotes: 0

Taryn
Taryn

Reputation: 247810

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?

Upvotes: 2

Related Questions