Showing posts with label corruption. Show all posts
Showing posts with label corruption. Show all posts

Wednesday, March 7, 2012

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,"Can you select that row with this where clause?

where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||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.|||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.