How to determine which row caused an error - ERROR CHECKING

Question

I saw that another poster (WolfiG) had asked a very similar question, but I don't see the answer. (I understand the error message, and am NOT looking for how to fix the error, I AM trying to determine whether there is any type of error checking (or debugging technique) to use for other errors.

I found similar code and added to it, to get each table in each database on a server...and to show the PKs and rowcounts. (Working on an inventory and then a data dictionary - but this is not the issue, I am just explaining how the code came to be.)

SO in this case I had already narrowed down the data somewhat (meaning I know which database I am querying and encountered the error) so there were "only" 81 tables (rows) that could have been in error. BUT of course that would require too much manual checking to find the problem, so I was hoping that there was a way to see which "t.name" was being read when the sub-query got the error. (I narrowed it down the the first sub-query by commenting out a line at a time (there were 10 and I just did not copy all of them here.)

Again the question is (and it looks like I am not the only person asking this type of question) - Is there a way to determine which row (data) caused an error in a query?

and in the example below, Is there a way to display the t.name (or other column's data) was the most recent when an error occured?

Having worked in Mainframe - you can look at a dump or buffer. But I guess I don't want to expect too much since I keep seeing cryptic messages, where obviously the system has the info but doesn't display it.

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

use [DBName]
SELECT '[DBName]' as DBName, t.NAME, it.xtype,i.rowcnt, 
   (select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=1 and k.id=t.id)as 'column1',
   (select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=2 and k.id=t.id)as 'column2'  
  from sysobjects t inner join sysindexes i on i.id=t.id   
    LEFT OUTER JOIN   sysobjects it on it.parent_obj=t.id and it.name = i.name
 WHERE  i.indid < 2 AND OBJECTPROPERTY(t.ID, 'IsMSShipped') = 0 

Thanks!

By the way, how I was able to find the row causing this error (in case this would help anyone else) is that I changed the first sub-query to "select count(c.name) from syscolumns" and then looked for any rows where the number was > 1.

Sebastian Meine Thank you, but now I've hit another problem. (I had to say that I am pretty much sadly disputing your comment that SQL is better than other DBs for the dictionary. GRRRR)

I was using " i.indid < 2" because - first off intially I was trying to get rowcnt and honestly the samples all use it. But I see how the query you pasted got far more rows and that the original missed - apparently because of the check: i.indid < 2

HOWEVER, with your query I am now getting many more occurences of the same table and I cannot see how it would be possible to "summarize" /ARGHHH while I am typing this up I am seeing that the COLS are not only primary keys and the same table name shows up (and with different row counts and it looks like in many cases there was a row for every column in the table - and NOT in other cases). So looking for a pattern, I found that MAYBE sysindexes.status = might limit my results - but while that was close there still were duplicate rows. Again ARGGGH

Answer 1

SQL Server does not provide the ability to do row by row debugging. Everything is handled as a set. If one element does not conform to the rules the whole query fails.

So, to find the offending row you need to write a separate query, as you already did.

The catalog vies in SQL 2000 have not been that great but since have been greatly improved and provide a lot more detail than most other RDBMSs out there. But since you seem to have the requirement to write one query that runs on all, try this:

 SELECT DB_ID() AS DBName,
        o.name,
        i.rowcnt,
        i.keycnt,
        cols.column1,
        cols.column2,
        cols.column3,
        cols.column4
 FROM   sysobjects o
 JOIN   sysindexes i
        ON o.id = i.id
 JOIN   (
          SELECT  k.id,
                  k.indid,
                  MAX(CASE WHEN k.keyno = 1 THEN c.name END) AS column1,
                  MAX(CASE WHEN k.keyno = 2 THEN c.name END) AS column2,
                  MAX(CASE WHEN k.keyno = 3 THEN c.name END) AS column3,
                  MAX(CASE WHEN k.keyno = 4 THEN c.name END) AS column4
          FROM    sysindexkeys k
          JOIN    syscolumns c
                  ON k.id = c.id
                  AND k.colid = c.colid
          GROUP BY k.id, k.indid
        ) cols
        ON i.id = cols.id
        AND i.indid = cols.indid

I do not have a SQL 2000 version running anymore to try this on, but I believe it will run.

Two things to be aware of:

1. The sysindexes.rowcnt value was very unreliable in SQL 2000. The value you find in sys.indexes in later versions is reliable but not ACID compliant.

2. There can be up to 16 kolumns in an index. I added 4 to the query and also added the keycnt column, so you know how many you are missing.

Answer 2

What I find useful is re-structuring the sql to make it easier to trouble shoot. I would avoid sub-queries in general unless they are absolutely needed.

Otherwise, if you do use sub-queries, you also need to account for the case where they return more than one result in a scalar context.

Typically most sub-queries like this can be re-written as joins. Then, you can also directly see what data is duplicated -- you can join the table to itself on the duplicated field such that the id's are not equal.