Table and other obj don't work when contains symbol space

We use Microsoft SQL Server Management Studio 14.0.17289.0 and Sql Assistant 10.0.187 Professional Edition
Table and other objects don't work when contains symbol "space"
We have next tables
[Some-Soft RU$Country]
[Some-Soft RU$Delivery]
[Some-Soft RU$Sales Order]
[Some-Soft RU$Worksheet Sales Entry]
[Some-Soft RU$Cust. Declaration Relation]
When I type
select * from [ - not working. no tips
select * from Some-Soft - when I type a space, the tips disappear
When i press Ctrl+Space on select * from berk-soft ru$C or select * from [berk-soft ru$C - no tips

A possible duplicate of this one >>>here<<<. Even though the OP stated it didn't work for him, I'd recommend using the same workaround I did there. I checked the method and it works. You could try that name matching method, at least until there's a fix for this one. It will probably stick even after the fix is available.

FYI, version 10.1 is going to be released this week. I expect it to support cases like this one.

Please try the latest version 10.1.246. It's now available for download or can be upgraded directly using Check for Update menu.

Thank you, it's already better, but

We have next tables
[SalesOrder]
[SalesReturn]
[RequestDone]
[Some-Soft RU$Country]
[Some-Soft RU$Delivery]
[Some-Soft RU$Sales Order]
[Some-Soft RU$Worksheet Sales Entry]
[Some-Soft RU$Cust. Declaration Relation]

When I type

select * from Some-Soft - when I type a space, the tips WORKING - IT'S OK.

When i press Ctrl+Space on select * from Some-Soft RU$Worksheet Sal - invalid tips. Is displayed SalesOrder and SalesReturn. Necessary only [Some-Soft RU$Worksheet Sales Entry]
When i press Ctrl+Space on select * from Some-Soft RU$Works - no tips.
When i press Ctrl+Space on select * from Some-Soft R - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from Some-S - invalid tips. Is displayed SalesOrder and SalesReturn

and with escape symbols
When i press Ctrl+Space on select * from [Some-Soft RU$Worksheet Sal - invalid tips. Is displayed SalesOrder and SalesReturn
When i press Ctrl+Space on select * from [Some-Soft RU$Works - no tips.
When i press Ctrl+Space on select * from [Some-Soft R - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from [Some-S - invalid tips. Is displayed SalesOrder and SalesReturn

I think I understand why you get what you get after Ctrl+Space. None of these case represent valid SQL syntax or syntaxically complete and guessable object name. The parser looks forward and looks back, and stops after the first space, it has no way of knowing how many words back it needs to scan to find something meaningful. Otherwise it would produce lots of false positive cases. You know the names only because you know what to expect. It doesn't know what to expect, it cannot help you with that. But if you add closing bracket ] after a complete name, then I expect it to work as it would look for a matching opening [ and then can match the object and help you with column names and other things.

When the cursor is before the closing ]
When i press Ctrl+Space on select * from [Some-Soft RU$Worksheet Sal] - invalid tips. Is displayed SalesOrder and SalesReturn
When i press Ctrl+Space on select * from [Some-Soft RU$Works] - no tips.
When i press Ctrl+Space on select * from [Some-Soft R] - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from [Some-S] - invalid tips. Is displayed SalesOrder and SalesReturn

The Ctrl+Space forces the popup, but there are 3 possible outcomes

1. White-space with DML statement keyword before: The cursor is after a white space. It scans text backward and finds preceding word. If the word s a recognized keyword which is part of a DML statement , like SELECT, WHERE, etc... then it grabs a bit more text before and after to reconstruct the statement context and depending on keyword shows either object or column list.

2. White-space with DDL or other non-DML statement keyword before: The cursor is after a white space. It scans text backward and finds preceding word. If the word s a recognized keyword which is part of a DDL statement like ALTER, CREATE, GRANT... or something else which is part of DDL and in a middle of it, it shows a list of likely keywords that can be possibly used in that context.

3. Non white space before: The cursor is after an alpha-numeric character: it tries to complete the word basedo n the names of matched objects in the cache. If such objects are found. If that didn't work, it checks for matching column names and if found, checks if there is a following FROM keyword and it can identify the name of the object after it, so it can filter matching columns for that object
only.

In all other cases "No matching .... found" messages are displayed.

In your case, it grabs whatever the word is before and works with that word, trying to complete as described above. It has no way of knowing how many more words back it needs to scan. What you get is a result of using non-standard naming conventions. Normally object and column names shouldn't have spaces and special symbols in their names.

Last edited by SysOp on Tue Feb 05, 2019 3:45 am; edited 1 time in total

	Quote:
	Normally object and column names shouldn't have spaces and special symbols in their names.

Tell it to the developers of "Microsoft Dynamics Navision". Where most tables, by default, have spaces and special characters.

May be add support escape symbols?
select * from [Some-Soft RU$Worksheet Sal]
if the cursor position is immediately before the "]", then find the "[" and show the correct tips.
And also support the correctness of the escape symbols in table name.
e.g.
select * from [Some-Soft RU$Work[she]]et Sal][/quote]

I'm really happy to submit an enhancement request to somehow improve that function, but I personally struggle how to formalize the rules and describe them.

Here is a silly example, I use ending | to indicate the cursor position

select * from [Some-Soft RU$Worksheet S|

In the above case, if Ctrl+Space is pressed, the preceding "word" starts with S. If there are any tables starting with S and I'm pretty sure there should be plenty of sys... objects, how would the assistance know that you don't want to complete S names and want "RU$Worksheet S" or "Soft RU$Worksheet S" or "Some-Soft RU$Worksheet S" instead? I see no formal logic there.

You say, find the he "[", but why would it look back for "[" here are other examples that make it really impossible

select * from [Some-Soft RU$Worksheet Sal]
select * from S|

Should it scan back all the text until it finds "[Some-Soft RU$Worksheet Sal] select * from S" and what to do with that?
How about the following?
select * from [Some-Soft RU$Worksheet Sal]
where S|
Should it stop after first space or continue all the way back to "[" or not?

Here is what I would like to suggest. If you know that most of your tables have the same prefix in their name like "Some-Soft RU$Worksheet " you can develop a code snippet with a custom hotkey assigned to it that would grab several tokens back from the current cursor position, piece them together and complete the name. Here is a generic template, I haven't actually tried it

	Code:
	$$SELECT TOP 1 name FROM sys.objects WHERE type = 'U' AND name LIKE '$CURRENT(param5)$-$CURRENT(param4)$ $CURRENT(param3)$%_$CURRENT(param2)$ $CURRENT(param1)$%'$$

This is not a very trivial exercise, but it might work. If snippets don't provide enough flexibility, perhaps you can develop a plugin for custom code scan and complete (the Plugin IDE is included with Pro Edition)

Last edited by SysOp on Tue Feb 05, 2019 1:57 pm; edited 1 time in total

I propose such a rule:
Add escape symbols to config. For MSSQL are [ and ]
If after pressing Ctrl + Space immediately after the cursor is ] algorithm such:
1) Find count ] to the right of the cursor but a pair of ] (]]) counts as one
2) Then move left and decrement count if found [
3) When moving left if found ] then increment count if it are pair. Else mistake
4) When count = 0 then show tips

Important! This only works if the ] immediately behind the cursor.

Example:
select * from [Some-Soft RU$Worksheet Sel]
On right ] and count = 1. Then move left until [. If count = 0 show Some-Soft RU$Worksheet Sel++++
select * from [Some-Soft RU$Worksheet [Sel]]]
On right ] and count = 2. Then move left unitl [, decrease count and move on until [. if count = 0 show Some-Soft RU$Worksheet [Sel]++++
select * from [Some-Soft RU$Wo[r]]ksheet [Sel]]]
On right ] and count = 2. Then move left unitl [, decrease count (count = 1) and move on until ]. see that pair ]] then increase count (count = 2), move on unitil [, decrease count (count = 1), move on [ and decrease count (count = 0).
Then show Some-Soft RU$Wo[r]ksheet [Sel]+++++

Maybe this only works for MSSQL or my situation.

The quoting symbols are already configurable for each database. And for MSSQL [ are the default as well as ANSI mandated double quotes, which are just as good. I will ask for the feedback from the developers and revert back to you.