473,588 Members | 2,486 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.
Jul 20 '05 #1
9 10757
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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...
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.

Jul 20 '05 #2
> 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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com... 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.

Jul 20 '05 #3
> 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/
Jul 20 '05 #4
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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...
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.

Jul 20 '05 #5
"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******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...
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.

Jul 20 '05 #6
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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...
"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******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.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**********@h otmail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...
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.

Jul 20 '05 #7
Rowland Hills (ro**********@h otmail.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
Jul 20 '05 #8
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******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.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?

Jul 20 '05 #9
mape1082
1 New Member
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
Jul 7 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
16283
by: Dave Wijay | last post by:
Hi Does anybody know how to programmatically select items (rows) of a ListView in C#.NET Thanks in advanc Dave
3
8647
by: gajaya1 | last post by:
I tried similar to sql server to get first 100 rows using "Select top 100 from table1" does not work? Is there another syntax?
2
100346
by: Jim H | last post by:
I am storing incoming data in memory using a DataTable. After I'm done retrieving the data I need to get the distinct rows. I tried using DataTable.Select but that doesn't work. If I have columns "col1", "col2", "col3" in my DataTable "Table1", I need to basically be able to do this: select distinct col2 form Table1 I tried doing this: DataRow ResultsRowArray = MyTable.Select("distinct col2");
0
1792
by: Raj Chudasama | last post by:
how can i select all rows in a datagrid also how can add checkbox to each of my rows in datagrid. I tried to use DataGridColumnStyle but failed try {
2
10706
by: Kevin Hodgson | last post by:
I have a standard .NET Datagrid (Databound to a SQL Dataset), and I need to select all rows of the datagrid when a button is clicked, to allow a user to copy the data, and then paste it into Excel or another application. They need to be able to do this because the application is running on a Terminal Server in Application Server Mode, and they need to copy the data to an Excel worksheet on their local computer. I can do this manually by...
5
2325
by: Silvio Matthes | last post by:
Hello, I'm new to the list and did not find a suitable answer to my question so here it is: I try to select the rows of a table where the content of a varchar-column is empty ('') and PostgresQL is doing a seqscan. I've tried this on a PostgresQL-Serverversion 7.3.4 and 8.0 beta1.
2
23927
by: Ettenurb | last post by:
I was hoping someone has come across this and came up with a solution. We have upgraded our custom software to us Infragistics UltraWinGrid 2006 CLR 2.0. The code below worked with a previous release of the UltraWinGrid. When the user clicks on the 'Select All' button this code is selecting all rows in the grid. The grid has band(0) and band(1) (Parent and multiple children)
2
7709
by: mokazawa1 | last post by:
Hi, I'm using stored procedures in DB2 UDB 8.1.2. In this stored, I execute a select for update command, opening a cursor. Then I update the rows using fetch and current of. The problem is that I want to return the rows for my Java application. But if I set the cursor of the select for WITH RETURN, I get the values, but I cant execute the update. If I dont use WITH return, I execute the update, but I cant get the result set. Any ideas.
1
3485
by: arockiasamy | last post by:
hi, How to select multiple rows in a table using java script? and that selected rows must be deleted? i have done for single selection. how it can be for multiple? come on help me.. i dont want to use list? . only table rows created by html. kindly help with regards sam
1
1997
by: mbewers1 | last post by:
Hi there, I'm having a strange problem whereby I can execute a select query in Oracle SQL Developer that rerturns some rows but, when this same query is placed into C#, I get no results. I've debugged the code and the date that I am passing in in the site is indeed being passed through to the query, but no rows are being returned. Please can you help me understand what code in my statement is causing the problem as similar queries in my...
0
7929
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
7862
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8357
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7987
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8223
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5398
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3887
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2372
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.