Results 1 to 8 of 8

Thread: Index Problem?

  1. #1
    Join Date
    May 2003
    Posts
    144

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    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 '."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    May 2003
    Posts
    144
    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

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    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?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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?

  7. #7
    Join Date
    May 2003
    Posts
    144
    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.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •