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" <rowlandhills@hotmail.com> wrote in message
news:4dbaff04.0401280612.282249e8@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" <cconover@commspeed.net> wrote in message
news:<1075221160.414137@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" <rowlandhills@hotmail.com> wrote in message
> > news:4dbaff04.0401270301.2dbbb5e1@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.