Temp Tables in MS Management Studio not recognized

The only difference I found so far, I get a red waive underline below WHERE indicating I have a syntax error, which is expected I don't see that underline on your screenshot.

Is that a SQL Server 2005 database? which edition?

	SysOp wrote:
	The only difference I found so far, I get a red waive underline below WHERE indicating I have a syntax error, which is expected I don't see that underline on your screenshot. Is that a SQL Server 2005 database? which edition?

Microsoft SQL Server 2005 - 9.00.4053.00 (X64)

I recommend to try reinstalling the SQL Assistant software.

Do a full uninstall of version 4.
Do a full uninstall of version 5.
Install version 5, just in case, the latest version is 5.1.10

Please let us know if that helps.

Thanks for hanging in there with me on this. I have the latest version, 5.1.10 installed, and now I'm noticing two distinct types of behavior. Going back to my original example query:

DECLARE @T TABLE(
MYNUMBER INT,
MYDOG VARCHAR(15),
MYPRICE NUMERIC(10,2)
)

INSERT INTO @T
(
MYNUMBER,
MYDOG,
MYPRICE
)
VALUES
(
1,
'FLUFFY',
29.95
)

SELECT t.mydog FROM @T t

When I start typing the SELECT statement (SELECT + space), I do get a popup box where @T is the first value in the box. It has an expansion "+" sign so that I can select the field names I need.

However, if I type the entire SELECT statement as shown above, and then I go back to "t.mydog", backspace over it and try to select a different field by typing "t.myprice", the normal behavior (at least in earlier versions) was that as soon as I typed "t.", I would get the popup box with my fields, and it would auto fill. With this new version, that does not happen...I get the popup box WITHOUT my table values in it.

Screen shots to follow.

This might be a silly question… but can you reproduce it a different database?

Here is another one, do you see any difference when you press Ctrl+Space after SELECT t. and when you remove all text between SEELCT and FROM and type t.

I tried two other databases. They are both SQL 2005, located on the same server as the one I've been using. I did NOT have the problem on the other two databases. I checked with our admin, and he said that the main difference between my "troublesome" database and these other two is that the "troublesome" one has namespace (sysdba), and the tables that I query from that database are all sysdba.<tablename>. Does that help?

Just to clarify so we can try to duplicate this, you're saying that on the "troublesome" machine you have a schema named 'sysdba'? Can you post screen captures for the schema configuration (or just the create script would be fine)?

Are you working as the user that owns the database? If not, what are both the users' default schema, and do you share a role with the owner? Can you add a screen capture of the database properties Permissions screen?

5.1.10
SSMS 2008
SQL 2000

I'm experience similar issues with this. Assuming:

	Code:
	DECLARE @T TABLE( MYNUMBER INT, MYDOG VARCHAR(15), MYPRICE NUMERIC(10,2) ) SELECT FROM @T t

Typing Select t. I would expect a list of columns from @t. Instead I get a list of tables. I closed everything, reloaded the default and re-tried with the same effect. I also uninstalled/reinstalled left everything as default. Same effect.

We are still unable to reproduce that issue. Let's assume that there is something in your database that is leading to undesired side effect. Can you reproduce it it in a different database? different server? What's your database default collation? compatibility level?

Can you post schema and database names from the server on which you're experiencing that issue? If you prefer not to post that info here, please email it to supportATsofttreetech.com.

Seems to work on SQL 2008, but not Sql 2000. I don't have a 2005 to test on. Both run the same collation. 2008 is x64. 2000 is x86 v. 8.00.2039. These are temp tables, so no schema needed. I reproduced the error on all 2000 servers and databases (including system, which haven't been modified).

	Quote:
	These are temp tables, so no schema needed.

I understand that. I'm interested in seeing schema names and database names on your SQL 2000 server. I'm guessing that some name might be affecting the working of SQL Assistant internal cache and somehow overshadowing or pushing out names of table variables and temp tables. If that issue cannot be reproduced on every system, it must be environment specific, we just need to figure out what in particular in your environment causes the difference in SQL Assistant behavior. We wouldn't know where and what to fix until we find that difference and reproduce the issue.

By the way, I tried the same SQL code with SQL 2000 and it worked like a charm, so that the issue is not SQL Server version specific.
[/quote]

SysOp wrote:

	Quote:
	These are temp tables, so no schema needed.

I understand that. I'm interested in seeing schema names and database names on your SQL 2000 server. I'm guessing that some name might be affecting the working of SQL Assistant internal cache and somehow overshadowing or pushing out names of table variables and temp tables. If that issue cannot be reproduced on every system, it must be environment specific, we just need to figure out what in particular in your environment causes the difference in SQL Assistant behavior. We wouldn't know where and what to fix until we find that difference and reproduce the issue.

I had some troubles in the past when there were schemas and databases given the same name. It was causing chaos. Are you referring to that case ?(http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=23129&highlight=elektra)

Email submitted with schema list from query. Email subject is the subject of this thread. Let me know if you want anything else.

Thank you. We will try to recreate your environment. I hope that will help to reproduce the issue.

I did notice that the list includes schemas the same name as databases on the server. However, the problem appears in other databases as well. HTH