Databases
StackOverflow2013
Postsdbo
POHow to identify row in query results that is causing fatal error?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to identify row in query results that is causing fatal error?
    primarykey
    data
    text
    <p>My query runs for awhile (outputting rows) and then stops, erroring out with:</p> <blockquote> <p>Msg 8115, Level 16, State 8, Line 5 Arithmetic overflow error converting int to data type numeric.</p> </blockquote> <p>That doesn't tell me WHICH row (of the rowset formed by the FROM clause - NOT which row in the SQL query) is causing this error.</p> <p>I used old trick of going to Query Options and unchecking "set arithabort" and "set ansi_warnings", which causes the query to keep running and put a NULL in the offending cell.</p> <p>The problem is that the result set can correctly have a NULL in a lot of places, so this doesn't help. (and the error message is equally vague: "Arithmetic overflow occurred.")</p> <p>I'm racking my brain on trying to figure this out - I've thought of using cursors or loops, but there are issues with each.</p> <p>What do you guys do when you get an error like this, in order to find out which row is causing it?</p> <p>UPDATE: Thanks for the helpful suggestions below; they are great and work well. However, before going to that trouble (there are alot of columns in this query), I just wanted to SEE the row that was causing the error. Luckily, I guessed that the last row in the query result set was the row right before the error occurred. When I raw a query to see the data right below that row (minus the calculated fields of course), I immediately noticed a larger-than-normal value in one of the INT fields.</p> <p>This seems like a good first step before going to the trouble of writing queries like the ones below - but thanks, I will save those and surely need them later when the problem isn't this obvious upon looking at the offending data.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USScottEdwards2000
    1. USScottEdwards2000
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to identify row in query results that is causing fatal error?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPerhaps you can add the query in the question.
      singulars
      1. POHow to identify row in query results that is causing fatal error?
      1. USypercubeᵀᴹ
    2. COIt's massive - and going through it logically to find error seems like a pain - seems like there should be a way to quickly find the offending row.
      singulars
      1. POHow to identify row in query results that is causing fatal error?
      1. USScottEdwards2000
    3. CODo you mean the row (like the `Line 5`) in the query or the row in the tables?
      singulars
      1. POHow to identify row in query results that is causing fatal error?
      1. USypercubeᵀᴹ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload