Author |
Message |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
The SELECT statement with space does not work the sql assist |
|
when I write the select statement for a table that has space in the name, sql assistant does not work.
is it a set up function?
example:
select * from dbo.my table
select * from dbo.[my table
select * from [dbo. my table]
select * from my table
Last edited by josemanuel.martin on Wed Dec 05, 2018 9:09 am; edited 1 time in total |
|
Wed Dec 05, 2018 7:21 am |
|
![](templates/GreenTech/images/spacer.gif) |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
|
|
with the management studio V17.9
version of sql assistant 10.0.187
|
|
Wed Dec 05, 2018 7:21 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
All the examples above are syntactically incorrect, the code is invalid and so no assistance can be provided for a broken code. It needs to be a meaningful SQL statement to be understood, for example
select * from dbo.[my table]
You can always press Ctrl+Space to force the popup. But unless the code is correct and the table name is properly qualified, you won't get any useful results.
|
|
Wed Dec 05, 2018 10:42 am |
|
![](templates/GreenTech/images/spacer.gif) |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
|
|
Hi ,
this is the real sentence what I try to write.
but does not wort with brackets
![](https://i.postimg.cc/LqCzmt6P/2.jpg)
|
|
Wed Dec 05, 2018 11:39 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
You get that after hitting enter to select one of the items in the popup?
|
|
Thu Dec 06, 2018 4:41 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
I'm unable to reproduce it. I created a new table named 206 European Resorts
when I type SELECT and get a prompt to choose table name, I select that table and hit Enter or just click it, it generates the following
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT
er.a,
er.b
FROM
[206 European Resorts] AS er |
when I type SELECT * FROM and get a prompt for a table name, after selection and enter I get
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT * FROM [206 European Resorts] AS er |
when I type SELECT * FROM 206 and then hit Ctrl+Space to force SQL Intellisense. Note that I do that right after 206 without space. Because this is the only table with matching name, it automatically completes that name as
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT * FROM [206 European Resorts] |
I then created a second table named 206 European Resorts$tests so that now I have 2 similar names. And it still works as expected. The only difference now is in the last case scenario, it doesn't complete the name automatically after 206, I get a popup with two matching names and I can select the one I want.
You are likely doing something else. Please describe your steps in more details.
|
|
Thu Dec 06, 2018 9:37 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
The same here. The only way I was able to achieve the code seen on the second image is to type it exactly that way. I'd suggest recording a short video about the happenings there.
|
|
Thu Dec 06, 2018 10:03 am |
|
![](templates/GreenTech/images/spacer.gif) |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
|
|
Hi ,
thanks for your reply.
With the old assistant version I only have to write the "Select * from " and select the table name but I have to write dbo. (shchema name) . So if If I continue writing give me more choice ( I have plenty of the tables all star with the same name).
From now with the new version I have to select the table name one by one, I mean to say drill down to select the specific table one by one instead of to write the table name. it does not recognize "the space" between the words.
What really I looking for is to write the "select * from " and popup the start of the all tables name and if i continue writing the tables names popup again the diff names.
To write the select statement.
first character of the table 206
now I have to following writing "space" (here does not work)
following writing "space", European , "space", "Resort", "symbol ($)" , the "G" word, (here it should popup all tables start whith the "G" word.
Many thanks
![](https://i.postimg.cc/3yJ4qVbm/3.jpg)
|
|
Fri Dec 07, 2018 4:34 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Okay, I understand now. Am I correct when I assume the following is happening? You try to filter the list of the names by typing the name from the start and as soon as you hit space the popup is gone, so you cannot fetch the desired table.
This is sort of a side-effect of the table created by using spaces instead of using underscore. I've never run into this issue because:
a) I rarely use names that explicitly require being delimited to not to cause trouble, and
b) the filtering (name matching) method I use (Name Contains Characters from Key String, Order by Best Match) is pretty much ignorant of this kind of issue, I never type space in filtering, even if it is present in the name.
With all that said, I still think this limitation in the filtering is somewhat bug-ish. Simply pressing space should not dismiss the popup if it can be considered to be part of a name. Not even '.' does that.
|
|
Fri Dec 07, 2018 6:10 am |
|
![](templates/GreenTech/images/spacer.gif) |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
|
|
Correct. this is what happen with the new version of the sql assistant.
version 5.2.59 allow space but whit this latest version of the assistant i can write with space.
any suggest?
many thanks
|
|
Fri Dec 07, 2018 7:21 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Correct. this is what happen with the new version of the sql assistant.
version 5.2.59 allow space but whit this latest version of the assistant i can write with space.
any suggest?
many thanks |
Temporarily, I'd suggest using Name Contains Characters from Key String, Order by Best Match as Name Matching Method and not using space for filtering until this kink is ironed out (if).
And even after that, I'd suggest keeping using this one, as it is vastly superior to the other one that filters at the start of the name. For example, you could narrow the list down for easy selecting of the table "206 European Resort$General Ledger Setup" by only typing "206GLS" (possibly omitting 206 as well) which would leave you with a very few candidates (or a single one, if you're lucky) in the popup. That stuff is simply powerful when it comes to speeding things up in a large set of tables.
|
|
Fri Dec 07, 2018 7:54 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Thanks all. We know now how to reproduce it. I'm going to submit a defect for this issue to have it fixed in some future maintenance release.
Also thanks for the suggestion. I think this is a very good one. Not only it would enable skipping the prefix with a space, in this case of many tables with similar names it would be also much more efficient, you won't need to type the same prefix again and again. You can type just the key part of the table name and locate it in the list much faster.
Here is how to change the "match" option.
Click Options dialog.
Select DB Options tab
Select SQL Server in the top left list
Expand Auto-complete Options section on the right side
Change Name Matching method
Click Ok
Last edited by SysOp on Sun Dec 16, 2018 1:28 pm; edited 1 time in total |
|
Fri Dec 07, 2018 10:24 am |
|
![](templates/GreenTech/images/spacer.gif) |
josemanuel.martin
Joined: 05 Dec 2018 Posts: 6 Country: Spain |
|
|
|
Hi,
I have tried it like your recommendation but it's does not work.
I'll waiting for the next release. Hope asap.
Many thanks
|
|
Fri Dec 07, 2018 10:50 am |
|
![](templates/GreenTech/images/spacer.gif) |
|