Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I've got a 400 line query in MS SSMS. When I run it I get:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Line 1 is just the "Select" statement. Is there anything I can enable in SSMS that will point me closer to the line with the error?
No.
SQL Server will just tell you the line of the statement that causes the error.
It won't even attempt to try and tie the error back to any particular line in a multiline statement and this wouldn't always be possible anyway.
For this error you likely just need to look for expressions using SUM, COUNT or +, *, - or similar that might overflow an int (remember the problem expression could exist in the definition of any views, functions or computed columns referenced in the query too).
For debugging purposes you can try suppressing the error with SET ANSI_WARNINGS, ARITHABORT OFF; and looking for any NULL in the result that might have been an overflowed int - but that will only help if the expression causing the error relates to a column that is projected in the resultset and the problem value is not subsequently filtered out and NULL are not generally expected for that column anyway.
The problem turned out to be one of four "DateAdd" functions. I found it by commenting out half the SQL then progressively commenting/uncommenting lines until I narrowed it down. The failing line was using an "Epoch msec" value. I hadn't read the fine print in the source table docs. The other 3 were using "Epoch seconds".
