SQLTeam.com | Weblogs | Forums

Arithmetic overflow error converting expression to data type int

tsql
sql2012
sql2008
sql2014
sql2008r2

#1

This is the script, and I am not sure how to fix it.

SELECT Extensions512_1mb.Extension, Sum([UserProfile12_7-512-1mb].KB) AS SumOfKB
FROM [UserProfile12_7-512-1mb] INNER JOIN Extensions512_1mb ON [UserProfile12_7-512-1mb].ComputerName = Extensions512_1mb.ComputerName
GROUP BY Extensions512_1mb.Extension;

Anyone?


#2

Can you show use the create statements for the tables? Specifically how all the columns in the query are defined?
I suspect either KB is not INT or only one of the two ComputerNames columns is.


#3

It think a non numeric data type for SUM would give you an "data type invalid for sum operator", or similar, error message - rather than assuming the results should be INT.

So I reckon its the JOIN

But that said I would have expected a data-type mismatch on the JOIN to include, in the error mesaage, both the data type and the value (e.g. string value) that it was having trouble with. "Expression" (and, indeed, "Arithmetic overflow") implies something else to me.

Perhaps with [UserProfile12_7-512-1mb] and or [Extensions512_1mb] is a VIEW with some expressions it it?

Or could the SUM of [UserProfile12_7-512-1mb].KB be exceeding max value for an INT?

perhaps try this?

Sum(CONVERT(bigint, [UserProfile12_7-512-1mb].KB)) AS SumOfKB