Rows not deleting

[Cineno]

Hello,

I've added a bunch of rows to a table, and then tried to delete all the rows using the standard "DELETE FROM table_name" statement. (For some reason the server errors out when I put the '*' in there.)

When I view the table itself, there are no rows in it, and the table appears to be blank, like I wanted it to be.

However, when I look at the table properties in SQL Server Enterprise Manager, it says there are still 8 rows in the table instead of zero. When I go on the web and look at the table properties through the website, it also says there are still 8 rows in the table.

What is causing this? How do I get this table to be completely blank, and have zero rows?

 

[AlexCuse]

I don't think you can use the * in a delete statement.

Are you getting the standard 'N rows affected' when you run your delete query?

What do you get when you try to select from the table?

I can't seem to replicate the error you are experiencing, my best advice to get it in a state where you can start from scratch would be to try

TRUNCATE TABLE [Your Table Name]

Hope it helps,
Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[SQLSister]

Sounds to me like someohow your systables have gotten out of synch with the real data. If truncate doesn't work, I would suggest scripting the table, then dropping it and recreating it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

I know this may sound dumb, but... double and triple check that you're actually using the same database. It is theoretically possible that Query Analyzer, Enterprise Manager, and the webserver are all pointing to different databases.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Cineno]

Thanks for the reply's!

AlexCuse - I don't get the "N-rows affected" message because I've been writing the statements in asp. I'll try to truncate it and see how that goes.

gmmastros - Yeah, I'm sure they're the same exact database, thanks though.


I'll try these suggestions out and let you know how it goes. Thanks!

 

[SQLBill]

Try running DBCC UPDATEUSAGE on the database. As SQLSister says, sometimes the information in the system tables gets 'out of sync'.

-SQLBill

Posting advice: FAQ481-4875

 

[Count430]

Remove the '*' from your statement and try something like the following "Delete from table where "Column" = "criteria"

The "*" and "DELETE" command is a risky combination to use on the a database. I think it's a safty precaution where you need to be more specific.

Or you could copy everything you want from the Table-1 to table Temp1. Next, use the drop command on Table1 and re-create Table-1 with the records you copied to temp1 with the Create table as Select into statement.

Not sure what approach is best for you, but good luck.

 

[Cineno]

Truncate worked great! Thanks for the help everyone.