Delete thrashed Header Record

Question

Delete thrashed Header Record

rickyTW

SSC Veteran

Points: 277
More actions
August 29, 2010 at 12:15 pm

#74079

I have a table with 95 records and one of them is thrashed and when I try to delete it, it gives me a funny error. When I try to UPDATE it (to make it "right"), it doesn't allow me to modify it. When I wrote a Delete script, it says:: 0 records affected.
The error message says:
"Data has changed since the Results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)"
There is NO other user on the system.
When I try to add the record in the Detail table, it says: Must exist in Header record before you can add it here.
It is a vicious circle.
Any idea how to FORCE the delete?
Thanks!
Richard

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

You are not going to like the answer - but, using Enterprise Manager to open a table and edit it is the problem. That GUI is broken, and - is also broken in 2005/2008 and should not be used to add/edit/delete data from a database.

You will be much more successful if you script the changes and issue the DELETE or UPDATE or INSERT statement in Query Analyser.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Check for triggers on the table.

_____________
Code for TallyGenerator

rickyTW SSC Veteran Points: 277 More actions · Answer 3

We tried that, we "saved" the triggers, removed them, tried it again, same message and then reinstalled the triggers.

Thanks for the suggestion.

I think we are down to having to remove the Constraints and then remove the bad record and then, and here is the key, recreate the Constraints. It is the last step, recreate the constraints (there are about a dozen of them for the header table), and we are a little worried about that last step.

THANKS!

Richard

rickyTW SSC Veteran Points: 277 More actions · Answer 4

PROBLEM SOLVED!!!!!!!!

Thanks to All Who helped. Here is the solution: Next to the table name there is property called Index. Open that, go the first Index, right-click and choose Rebuild.

Once you know where to go it is soooooooo simple.

I really belong in the SQL Newbies class.............

Thanks!

Consider this issue closed!

DougGifford SSCommitted Points: 1815 More actions · Answer 5

richard-677617 (8/30/2010)
PROBLEM SOLVED!!!!!!!!
Thanks to All Who helped. Here is the solution: Next to the table name there is property called Index. Open that, go the first Index, right-click and choose Rebuild.
Once you know where to go it is soooooooo simple.
I really belong in the SQL Newbies class.............
Thanks!
Consider this issue closed!

Maybe I'm the Newbie...

You say "Next to the table name there is property..." Where and what are you using to look at the Table name?? You posted the thread in SQL 2000 so I am looking at the SQL Server Enterprise Manager for SQL 2000 and I can not find any where that a Index property is listed next to a table name.

Can you point me in the right direction?

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

rickyTW SSC Veteran Points: 277 More actions · Answer 6

I am sorry, this is in SQL 2005.

Open the Database. Underneath Databases, you'll see Tables, Views etc

Next to each Table there is a + sign. Click on the + sign.

THANKS!

ahiresh SSC Rookie Points: 31 More actions · Answer 7

Thanks a lot as i did a lot of trail and error on this and got the solution from this link, thanks again it did work for me.