Ghost record in SQL Server 7 table

[Sashanan]

In my database, a simple one-column table containing unique company names has a ghost record. It shows a value there, but attempting to use it as a foreign key gives a key constraint, and when I try to remove it directly from the table view, I get an error stating the record appears to have been removed already. But then when I refresh, it's still there.

If I execute a SELECT query on it, I see it. If I try an UPDATE or DELETE query on it, it says it has executed it successfully, but nothing has actually happened. If I try to create a duplicate record with the same name, the database accepts it despite the column being primary key (and that duplicate record then works fine and allows itself to be removed again).

For all purposes, the record in question isn't there, except that I do see it and it defies removal. How do I get rid of it?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[JayKusch]

If it is a simple one column table, can you export the records out to a text file, truncate the table, open the text file and remove the errorneous record ... if it is there and then re-import the text file back into the table?

I know if you have FK set up this may not work.

If all else fails, export data, drop/rebuild table and then re-import.

Thanks

J. Kusch

 

[Sashanan]

Probably going to be a table drop, yeah. At least my userbase is small enough that I can probably get away with breaking away the table and quickly putting it back again with minimal downtime.

Will let you know how it went.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[Sashanan]

Simply migrating the data to a delimited text file, deleting the bogus record with Notepad, reimporting the file to a new table, and then replacing the old with the new allowed me to fix the problem with exactly two minutes downtime. I suspect nobody even noticed.

Apparently this was some sort of import problem, as it occured with 5 year old legacy data from an Access 97 database. The record was meant to say 'PR-MAG-Z', but in the text file it showed up as 'PRÍMAGÁZ'. Interestingly, doing a SELECT query on either the - - or the Í Á variant from the Query Analyzer returned 0 results.

Ah well, the record attached through FK had practically no data anyway. Ghost record wiped, all is well again until the next problem.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)