Cache anomaly?

In some of my queries I use schemas other than "dbo" and sometimes that seems to end up in some odd behavior.

I type the following:

	Code:
	SELECT * FROM ele

At this point I can see the schema "elektra" in the popup with a small "+" in front of it. If I press the right cursor button, then it unfolds and I can see all the table names belonging to that schema.
If I press enter, it pastes "elektra" into the code. Now, if I press "." it should show the table names belonging to the schema but sometimes it just does not. The result is an empty popup window as if it has been removed from the cache. If I remove the "." and press ctrl+space the popup shows the schema name but without the "+" sign and it cannot be unfolded. Pressing F5 refreshes the cache and "+" reappears and so does the ability to unfold, but the popup is filled with table names from "dbo", not "elektra". I cannot reproduce that every time, it happens randomly and I could not figure out the reason for it.
Could you verify that, please?

Which editor are you using (type and specific version)?
Which version of SQL Assistant?

SA version is 5.0.97

Seems to be editor-independent, I have experienced it in SMSS 2k8, Notepad++ v5.3.1 and Windows built-in Notepad on a 2k3 Server.

Ctll+space after the schema name without the ending dot, makes it to offer schema name auto-complete list. That's why you don't see the place. If you enter "electra." and then press Ctrl+Space, that should show the list of tables.

Let's try to establish the facts we need to know to properly troubleshoot this issue.

Does it show table names for other schemas, for example, for dbo?
Does it work with electra.[Ctrl+Space]?
Do you use customized versions of database queries in SQL Assistant settings?

Does loading default SQL Assistant settings resolve the issue? To load default settings, make a copy of your current %APPDATA%\SQL Assistant\5.0\SQLAssist.sas file. Open SQL Assistant settings, click Import/Export button, click Default check box, click Import. click OK to apply them and close Options dialog.

	Quote:
	Does it show table names for other schemas, for example, for dbo?

dbo is the default schema for my login/user and typing the table with or without the dbo schema works normally. It gets qualified with the schema name if it wasn't given. Haven't tried it with other schema beside elektra (it is the only one yet).

	Quote:
	Does it work with electra.[Ctrl+Space]?

Depends. Sometimes it offers the tables, sometimes the popup is empty (I don't know why). Pressing F5 fills the popup but with tables belonging to dbo as if it would suddenly change the schema, elektra tables are not visible after that.

	Quote:
	Do you use customized versions of database queries in SQL Assistant settings?

None I'm aware of. But I'll restore default settings to see what happens.

And an update for http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=23106&start=0&postdays=0&postorder=asc&highlight=

I gave up on snippets for INSERT INTO statements for non-default schema but now I see that using simply the INSERT INTO statement with popup and the right arrow-scroll-select method does not work the same way as it does with default schema tables. For defaults it code-completes the statement with qualified table name, brackets, its fields and the VALUES code piece. When using a table belonging to another schema, it does not. It only pastes the schema.tablename into the code and stops there.

Perhaps these issues are related.

UPDATE: reverting to default settings did not help.

This is some kind of an anomaly. Are there any objects in electra schema with unusual names? or very long names? Something else, that perhaps may cause screen rendering issues?

Which collation is used for this database?

Since you are using SQL Server, does it happen when referring to objects in non-dbo schemas in the AdventureWorks database?

	SysOp wrote:
	This is some kind of an anomaly. Are there any objects in electra schema with unusual names? or very long names? Something else, that perhaps may cause screen rendering issues?

What would you define as unusual? There were a few with brackets but I renamed them all. As for their length, the longest of them is 35 characters, that should not be a problem either. It is definitely not a screen rendering issue since it cannot be unfolded when the "+" is missing.

	Quote:
	Which collation is used for this database?

SQL_Hungarian_CP1250_CI_AS

	Quote:
	Since you are using SQL Server, does it happen when referring to objects in non-dbo schemas in the AdventureWorks database?

AdventureWorks is not present on the server, and as I see, none of the other databases contain tables belonging to schema other than dbo. I'll have to create a test database.

May be this cane be used as a quick test

	Code:
	CREATE DATABASE testdb USE testdb CREATE SCHEMA electra CREATE TABLE electra.test1(colA INT PRIMARY KEY) CREATE TABLE electra.test2(colB INT, colA INT FOREIGN KEY REFERENCES electra.test1 (colA) ) ... refresh SQL Assistant cache using Refresh command in the right-click menu ... type SELECT * FROM electra. ... SELECT * FROM electra.test1 JOIN check if this works USE master DROP DATABASE testdb

Thanks for the code. I made my testdb but did not have the time to post the results until now. Nevertheless I tried you code too, to see if it makes difference. It seems that I got it. Besides the elektra schema in my current database there is another database that is called ELEKTRA. This gave me an idea. I did not think it would matter but it seems it does.

I created the testdb with schema testschema. I was unable to summon the problem. Then I created a database called testchema and there it was again. Having a database (not necessarily the same I'm working in) as the schema I want to use makes this confusion.

Oh... I get it. The db name overshadows schema name. That's why when you type electra, the tool gets confused as it thinks you have entered db name.

If you don't work with the electra database, you can tweak the code of "Databases (MSSQL)" query in SQL Assistant options and filter out "electra" in the WHERE clause, something like AND name != 'electra'

Problem solved. Thanks!