Geek asks and answers

How to determine which line caused an error - ERROR CHECK

I saw that another poster (WolfiG) asked a very similar question, but I do not see the answer. (I understand the error message, and I am NOT looking for how to fix the error, I am trying to determine if there is any type of error checking (or debugging method) for other errors.

I found a similar code and added to it to get every table in every database on the server ... and show PK and rowcounts. (Working with inventory, and then using a data dictionary, but this is not a problem, I just explain how this code became.)

SO in this case, I already narrowed the data a bit (this means that I know which database I am querying and encountered an error), so there were “only” 81 tables (rows) that could be erroneous. BUT, of course, this will require too much manual checking to find the problem, so I was hoping there was a way to see which "t.name" was being read when the subquery got an error. (I narrowed it down to the first subquery, commenting the line at a time (it was 10, and I just didn't copy them all here.)

Again a question (and it seems that I'm not the only person asking this question) Is there a way to determine which row (data) caused an error in the request?

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

Mainframe - . , , , , , , .

Msg 512, 16, 1, 1 1 . , =,! =, <, < =, > , >= .

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

!

, , ( , - ), "select count (c.name) syscolumns", , > 1.

, . ( , , SQL , DB . GRRRR)

"i.indid < 2" , - rowcnt, , , . , - , - : i.indid < 2

, , , "" /ARGHHH , , COLS , ( , , ), ). , , , MAYBE sysindexes.status = - , . ARGGGH

+5
2

SQL Server . . , .

, , , .

SQL 2000 , , . , , , , :

 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

SQL 2000, , , .

, :

  • sysindexes.rowcnt SQL 2000. , sys.indexes , , ACID.

  • 16 . 4 , keycnt, , .

+3

, , - sql, . , .

, , , .

, , . , - , .

+1

More articles:


All Articles