Index Problem?

If a table has corrupted indexes, can I still query against it in SQL Enterprise?

I have the following statement as the data source of my data list.

SELECT productid, prodname, prodprice from products inner join vendors on products.vendorid = vendors.vendorid where vendorid = 185

The data list takes long time to populate and returns no row. I thought maybe the index of my Products table got corrupted, so I ran the same statement in SQL statement and got 3 rows of data. I am clueless now. Any suggestion?

Thanks

Bugme.Sounds to me the table is locked or at least a few rows.

on the side: I'd expect something like "Ambiguous column name 'vendorid '."|||Can you look at the sticky and post some more info.

Have you tried DBCC CHECKTABLE?

And what do you mean SQL Enterprise?

Space...the final frontier......|||It's the Enterprise Manager.

I ran DBCC CHECKTABLE and got the following --

There are 8832 rows in 82 pages for object 'products'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since I didn't get any error from DBCC CHECKTABLE, does it mean that the index on products table is fine?

If the table is locked, how can I unlock it?

Thanks

SHK|||The EM might do the locking, I'm not sure how it's used. What happens if you'd exit the EM, run the SQL Analyzer?|||I am not sure I understand where you are going with the question. Theoretically, you can query a table that has corrupt non-clustered indexes, so long as you are not using the corrupt index to access the table. Is your base problem that the query is just slow?|||ProductID is the clustered indexed field and I tried to query against ProductID in EM, I got data.

The problem is when I run the query in my application, it returns no data. When I run the same query in EM, I get data. The problem only happens to vendorid 185. If I run the application with different vendorid, it works just fine. I checked the index on the vendors table too and it's fine.

Weird! Help.

SHK.|||Well, we can rule out miracles. Post the exact query from the .asp page, and the query you run from Query Analyzer. I expect a join has been fouled up, or the where clause in the .asp page is more involved.