INFORMATION_SCHEMA Glitch

BrianFinkel

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

Bart Read

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

BrianFinkel

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.

Bart Read

OK thanks, sounds like a bug to me then. I'd still recommend keeping that option checked.
Thanks,
Bart

BrianFinkel

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.

Bart Read

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

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

BrianFinkel

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.

Bart Read

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