INFORMATION_SCHEMA - Beyond Glitch!

BrianFinkel

So I installed SSMS 2005 and upgraded to Sql Prompt 3.7 as per your suggestions. Now the INFORMATION_SCHEMA views do not appear no matter what I try.

This thread has the original problem I encountered:
http://www.red-gate.com/messageboard/viewtopic.php?t=6290

In fact, after installing SSMS and then re-installing Sql Prompt 3.7, I had the exact same problem as in the original thread -- the views were not included in the candidate list, even though INFORMATION_SCHEMA was, then I disabled and re-enabled the "Filter candidate list" option, tried again, and it did work.

But then I created a new query, tried once again, and now I can't get it to work no matter what I do -- in SMSS or Query Analyzer. I tried refreshing the Master db cache as well as the cache for the db I am working in.

This is driving me bananas because I am all over those INFORMATION_SCHEMA views right now!

Thanks,
Brian

Bart Read

Hi Brian,


That's really odd. I've just got the same thing here. If I'm connected to master (on SQL Server 2000) then I can access everything in INFORMATION_SCHEMA no problem, but if I'm connected to any other database, such as Northwind, I can see the INFORMATION_SCHEMA object itself, but none of the views belonging to it.

My apologies for this. Please bear with us and we'll take some time to look into it.


Thanks,
Bart

BrianFinkel

Just wondering what the status is on this and whether I need to do something special to make a real support incident out of it.

Thanks,
Brian

Bart Read

Hi Brian,


Sorry for the delay. We are spending time on this at the moment, although please feel free to mail support@red-gate.com to get a ticket assigned. The Prompt team is new to the job this past month or so, so it's taking them a while to get to grips with things.

I've actually tried debugging this but (typically) it's a Heisenbug: happens with 100% reproducibility until you attach a debugger, when it disappears completely. I suspect some sort of race condition because we populate the system objects differently for SQL Server 7/2000 versus 2005/2008.

For 2005 and beyond it's easy: all the system catalog stuff is exposed in all databases, so we just read the meta-data for the database you're connecting to and all the system objects come for free. Unfortunately this doesn't work for SQL Server 7 and 2000. In this case we have to also load the master database and merge the objects into the collections for your current database. Somewhere in the midst of the merging the INFORMATION_SCHEMA objects are getting lost. At the moment I'm just not sure where.

I have a suspicion that as soon as we've found the problem it'll be a fairly straightforward fix, but at the moment it's not obvious what's going wrong.

I'll let you know when we have anything else, and apologies again for the delay.


Thanks,
Bart

Bart Read

Oops, looks like I spoke too soon there. Apparently this has been fixed, which is great.
Thanks,
Bart

BrianFinkel

I spoke to Tanya on the phone yesterday and, during our conversation, she suggested switching databases, refreshing, switching back, and refreshing again, and that seemed to clear it up for the moment. I don't know why I didn't try that before -- perhaps I had some "troubleshooting fatigue."

I have not had quality dev time with it, though, so I can't really sign off just yet. But I'm optimistic. And I should have some time today.

This was the biggest problem I've had with the program, so my fingers are crossed.

Bart Read

Hi Brian,


OK thanks, that's good to know. I had a chat with Tanya earlier as well, and since this is obviously a workaround rather than a fix we've still got a developer looking at it now.

If you run into anything else please let us know. The feedback is always welcome.


Cheers,
Bart