What are the challenges you face when working across database platforms? Take the survey
Options

INFORMATION_SCHEMA Glitch

BrianFinkelBrianFinkel Posts: 33
edited January 16, 2008 1:31PM in SQL Prompt Previous Versions
I just downloaded the 3.6 eval and encountered the following problem. I am using SQL 2000 with Query Analyzer, and I enabled the "System Objects" candidate types in the Sql Prompt options.

After typing "SELECT * FROM INFORMATION_SCHEMA." in QA, the candidate list would show "No suggestions". I tried several times, refreshing the cache, etc. Did not work.

Then, I unchecked "Filter candidate list by owner/schema prefix", an option which I admit I do not fully understand, even after reading the pop-up help description for it, and I tried again. This time, the info schema views are available in the candidate list, although I had to switch to the "Views" candidates to see them.

Next, I went back to options and re-checked the "Filter candidate list by owner/schema prefix" option, then tried again. Now it works as expected! The views are immediately suggested as candidates.

What happened? Is this a bug?

Thanks,
Brian
Using Sql Prompt 3.7 with SQL Server 2000, Query Analyzer, and 2005 Mgmt Studio.

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Hi Brian,


    It sounds like it might still have been retrieving the meta-data for the database (including system tables, views etc) when you were first typing. If you want to filter by INFORMATION_SCHEMA you should make sure that "Filter candidate list by owner/schema prefix" is checked, otherwise you'll see other database objects that don't belong to INFORMATION_SCHEMA after the dot.


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    A status box is displayed above the system tray while Sql Prompt gets the metadata, and I generally don't do anything while that is visible.

    Unless it continues to fetch data after the box disappears, I'm fairly certain it was done.
    Using Sql Prompt 3.7 with SQL Server 2000, Query Analyzer, and 2005 Mgmt Studio.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    OK thanks, sounds like a bug to me then. I'd still recommend keeping that option checked.
    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    I am using Sql Prompt with the option checked, and I understand the benefit of using it.

    I only mentioned it to describe how disabling it, trying again, then re-enabling it somehow caused the data to appear and the problem to be fixed.

    I'm trying to be as detailed as my patience will allow to facilitate reproducibility and bug analysis.
    Using Sql Prompt 3.7 with SQL Server 2000, Query Analyzer, and 2005 Mgmt Studio.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Thanks Brian, it's much appreciated. I know how frustrating it can be when software starts misbehaving.

    The only thing I can think of at the moment is that there might be some sort of race condition that means the system objects are not being fully populated before the meta-data loading and completion list population is finalised. We use a different population method on SQL Server 2000 to 2005 and above because the only way we can get the system objects on SQL Server 2000 is to actually load the meta-data for the master database. For 2005 and above we don't have to do this because it's exposed in every database.

    It's a while since I wrote the code, but I suspect the reason unchecking and checking the option works is that when you check it again all the system objects will have been fully loaded, so when the completion lists are repopulated (unchecking and checking this option would force this, as long as you close the Options dialog in between) the INFORMATION_SCHEMA objects appear correctly.

    For now unfortunately I can only say we'll try to get this fixed in the next release (I'm pretty sure it would still happen in 3.7, although if you fancy giving it a try there's no reason not to).


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Another thing I just thought of, only because it's just caught me out on SQL Server 2000, is that you might not have system object display enabled (it's off by default).

    You can find this option in SQL Prompt > Options > Listed Candidates > Candidate Types and Filters. Under the "Candidate types" heading, ensure that "System objects" is checked.

    I should add that obviously INFORMATION_SCHEMA should only appear if this *is* checked, however it could be that the bug is that some, at least, of the INFORMATION_SCHEMA objects appear when it isn't under some circumstances.


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Is checked, was checked.

    And just to be clear, this issue is not currently a problem for me. It was a temporary, odd situation after install.
    Using Sql Prompt 3.7 with SQL Server 2000, Query Analyzer, and 2005 Mgmt Studio.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    OK thanks, glad to hear that at least. I just wanted to be sure with that last post.

    This bug is in our tracking system so hopefully it should be fixed at some point in the future.


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.