Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Syscolumns has a different length value then actual table

    Syscolumns has a different length value then actual table.

    I have a table named TEST
    Lets say i have 3 columns

    Account nvarachar(7)
    FirstName nvarchar(12)
    LastName nvarchar(30)

    This is what shows in the actual table form design mode in Enterprise Manager

    When i try to return all rows, it gives me an error - don't recall exactly but has to do something with can't find table object

    when i look at the syscolumns for that table it looks like this

    Account nvarachar(14)
    FirstName nvarchar(24)
    LastName nvarchar(60)

    Exactly DOUBLE the length of the actual table.

    When i tried to do Select * from TEST from Query Analyzer, i mostly got an error, but one time i did get a result and that result had 3 records of corruption, nothing related to the real data (total number of records was around 18,000)

    Any Ideas, i checked KB, couldn't find anything useful. I reinstalled service pack3. I reinstalled MDAC as well just in case (suggested in a KB)

    Also i imported a backup of that table, and that table was fine BUT when I deleted the original and renamed the backup to the deleted one, i got the same problem.

    I can't export that bad table either.

    Any Suggestions, info?
    Beyond Limitation

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    syscolumns holds the length of the field not the number of characters.
    nvarchar is stored as two chars per character hence your value.

    Sounds like you have a corrupt table.
    Try dbcc checktable
    You should also check the entire database.

    bcp that data from the table out - it will fail when it gets to the corrupt page but you should get something.
    You can then use any indexes to get further data from the table and insert it all into a new table.

  3. #3
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    Sorry for late response. i Was away for the weekend. after doing
    dbcc checkdb i got

    Server: Msg 2537, Level 16, State 40, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 0. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 1. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 2. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 3. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 4. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 5. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 6. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 7. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 8. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.
    Server: Msg 2537, Level 16, State 1, Line 1
    Table error: Object ID 724197630, index ID 0, page (1:8400), row 9. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 14 and 7.


    and alote more of the same errors repeated - and after that i got this

    Too many errors found (201) for object ID 724197630. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".
    There are 0 rows in 1111 pages for object 'GMVIDEO'.
    CHECKDB found 0 allocation errors and 14528 consistency errors in table 'GMVIDEO' (object ID 724197630).

    where gmvideo is the table of problems
    ***There are 0 rows in 1111 page**** not good ?
    Beyond Limitation

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> ***There are 0 rows in 1111 page**** not good ?
    I think that's a fair summary.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by nigelrivett
    >> ***There are 0 rows in 1111 page**** not good ?
    I think that's a fair summary.
    Don't sugar coat it Nigel....


    Back up? What back up....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    I do have backups, but i have no idea when this problem really occurred. Also, too many transactions have been process to restore an older backup. (the problem has been existing for more than 5 days).

    Also i avoided this problem by just importing that table again (i lost maybe 5 transactions)

    but for future reference - what should i do ? if anything (besides restore backup

    ========================================
    ALSO nigelrivett when u said

    "syscolumns holds the length of the field not the number of characters.
    nvarchar is stored as two chars per character hence your value"

    most of the tables in syscolumnn shows the correct length (same as) the actual table column size.

    some (like 3-4) don't. I use syscolumns and sysobjects in a few of my vb programs to get structures and stuff.. i want to avoid having to access incorrect info

    One thing i also notice the correct table xtype number are 167
    and all the incorrect are 231
    I don’t know what that means – but it might help you ?
    Beyond Limitation

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    nvarchar is stored as two chars per character hence your value"

    the correct length columns with xtype 167 will be varchar which is an ascii datayype stored as one byte per character.
    The incorrect ones with xtype = 231 will be nvarchar which is a unicode datatype stored as 2 bytes per character.

    see varchar and nvarchar in bol.
    The same will apply to any nchar columns.


    >> but for future reference - what should i do ? if anything (besides restore backup

    I would advise restoring the backup. Don't try to contiue using the database as there may be other problems with it.
    You can try a checkdb with repair or rebuilding indexes on the table but I would copy everything to another database before continuing.

  8. #8
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    GOT YOU.

    I actuallly understand.

    thnx for the help
    Beyond Limitation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •