Rowland,
First, only "rebuild" if you are sure the table is corrupted. I have
included how to rebuild below, but I've never seen a table corrupted in SQL
Server 2000, which isn't to say it isn't happening.
One more thing to look at first. Does "TABLE_NAME" exist in your db
with multiple users? IE: dbo.TABLE_NAME and myuserID.TABLE_NAME. And, are
you doing both queries from the same place? ie: both in SQL Query
Analyzer, or are you doing one in a procedure or application? It just
sounds like you might be querying 2 different tables. I think this is a
much more likely reason than that the table is corrupted.
By "rebuilding the table", you can just drop and re-add if you don't
need to keep the data in the table, but, if you want to keep the data, this
is what I would do.
1. Create another table with the same structure
2. Insert all the data from the corrupted table (we're assuming it's
corrupted) into the new table(ie:
insert into new_table (col1, col2, col3...)
select col1, col2, col3... from old_table
3. Drop the old table
4. Rename the new table to the old table name (ie: exec sp_rename
'new_table', 'old_table'
You'll get a warning that says:
"Caution: Changing any part of an object name could break scripts and stored
procedures.
The object was renamed to 'TemptblName'."
Which you should definitely consider before dropping the old table. ie:
What foreign keys, triggers, and views reference the old table. I think the
views will be ok, but the FK and triggers will need to be re-added.
Best regards,
Chuck Conover
www.TechnicalVideos.net
"Rowland Hills" <ro**********@hotmail.com> wrote in message
news:4d**************************@posting.google.c om...
"TABLE_NAME" is indeed the name of my table, not a view on it,
unfortunately!
I've tried the "update statistics TABLE_NAME" command, which had no
effect, and the "DBCC CHECKDB" which reported no errors.
When you say "rebuild the table", do you simply mean drop it and
create it again, or so I need to do something more?
Thanks for your help,
Rowland.
"Chuck Conover" <cc******@commspeed.net> wrote in message
news:<10***************@news.commspeed.net>...
Rowland,
If "TABLE_NAME" is a view, then I have seen this kind of problem if
there are subqueries in the view that retrieve multiple rows. But even
that would depend on a where clause, which you don't have.
If "TABLE_NAME" is truly a table, you probably need to rebuild the
table. First try rebuilding the table statistics, but I don't think
that will solve the problem.
Best regards,
Chuck Conover
www.TechnicalVideos.net
"Rowland Hills" <ro**********@hotmail.com> wrote in message
news:4d**************************@posting.google.c om... I have a table which is returning inconsistent results when I query
it!
In query analyzer:
If I do "SELECT * FROM TABLE_NAME" I get no rows returned.
If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.
In Enterprise manager:
If I do "return all rows" I get 4 rows returned, and the SQL is listed
as being "SELECT * FROM dbo.TABLE_NAME".
I've tried adding the "dbo." before my table name in QA, but it seems
to make no difference.
I'm using SQL Server 2000, which is apparently 8.00534.
Can anyone help me, or give me ideas about what to check?
Thanks,
Rowland.
