SELECT * not returning any rows, but SELECT COL_NAME does!

[Rowland Hills]

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.

[Hari]

Hi,

Can you execute the below statement from QA,

update statistics tablename

After this try to execute select * from table

Thanks
Hari
MCDBA
"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.

[Tibor Karaszi]

> 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.
Scary... I'd do a consistency check for the database/table.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=d...blic.sqlserver
"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.

[Aaron Bertrand - MVP]

> I'm using SQL Server 2000, which is apparently 8.00534.

In addition to the other suggestions, you might want to make sure you're at
least up to SP3 (8.00.760).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Chuck Conover]

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.

[Rowland Hills]

"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.

[Chuck Conover]

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.

[Erland Sommarskog]

Rowland Hills (ro**********@hotmail.com) writes:

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.

When you tried the query in QA, did you try both text mode and grid mode,
or only one of them? Since you get data in Enterprise Manager, the
data seems to be there, unless you are looking in different databases
or different servers. But one possibility is that there are some strange
character in one column which confuses the output for Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rowland Hills]

Well, I've finally solved the problem.

It turned out to be just one column which was causing the problem.
Dropping that column, then recreating it solved the problem. Still
don't know exactly what was wrong, but one of my colleagues uses three
different keyboards (UK, US and Swedish) and suspects that he may have
entered a combination which resulted in an illegal character whilst
defining this column or entering data into it, and that's what caused
the problem.

Anyway, thanks for everyone's helpful suggestions.

Regards,

Rowland.
"Chuck Conover" <cc******@commspeed.net> wrote in message news:<10***************@news.commspeed.net>...

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.

<VARIOUS USEFUL REPLIES SNIPPED>

> 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?

[mape1082]

I had exactly the same problem and I found that the SQL Query Analizer had the Options>Results>Max Chars per Column set to 32. I set it back to 8192 and then it worked fine.

:D