Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: How to delete row 169441?

    MSSQL database table DATE field has corruption. The error message says it is at row 169441. I cannot finish an export, browse to the record or delete it. I got the bad record below by exporting to a text file and this was the last record before it stopped.

    This is the bad record. 2nd field is DATE
    "0000000004015 ",15955-04-30 103:21:55.207000000,"

    This is a good record immediately preceding the bad one.
    "0000000004015 ",2006-01-22 00:00:00,"

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Can you select that row with this where clause?

    where date > '1/1/2050'

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    No. where date > '1/1/2050' returns 'invalid date data'

  4. #4
    Join Date
    Apr 2005
    Posts
    67
    If there is no "key" on this table you could try:

    Backup your database

    Set RowCount 169440 -- Process only first nnnn rows.
    Select * Into #TMP_Table from YourTable
    Set RowCount 169441 -- Process only first nnnn rows.
    Delete From YourTable -- Should delete all rows copied plus the bad one.
    Set RowCount 0 -- Process all rows
    Insert Into @TMP_Table Select * From YourTable -- Copy the rest

    This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!

    OR

    You could try some of the undocumented DBCC commands to force the data.

    Good luck.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rlassiter
    This is the bad record. 2nd field is DATE
    "0000000004015 ",15955-04-30 103:21:55.207000000,"

    This is a good record immediately preceding the bad one.
    "0000000004015 ",2006-01-22 00:00:00,"
    You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?

    We can't diagnose the problem like this.
    Please take the time to post correct data with a complete and careful description if you want correct answers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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