Why is SqlComplete suggestions/intellisense dependent on other test in the editor?

[Thenrich]

I am of the type who types several kind of SQL queries and proc calls in the same editor tab. Sometimes SQLComplete doesn't list the tables names, the list is either blank or a list of SQL keywords. No table names. Even a simple select * from and I hit tab, no tables show up. I find out that I needed to comment out a VALID statement before it in the editor.. or fix any errors in the editor which are not related to my current statement. I don't know why SQLComplete works like this but I find it annoying. OK so I fix any errors there are but I don't understand why I have to comment out one or more prior valid sql statements.

I have used other Intellisense type of SSMS add-ons and they don't behave like that. They don't care if there are unrelated errors in the editor. (version 5.5.145)

[alexa]

Could you please provide us the script and describe the steps to reproduce this issue step by step? A video or screenshot demonstrating this issue would be also of help.

Additionally, please send us the system information generated by clicking the 'Send Info' button in the 'System Info Report Contents' window invoked by selecting 'SQL Complete -> About...' from the main menu of SSMS and then clicking the 'View System Info' button in the dialog.

You can reply straight to our support system at supportATdevartDOTcom

[Thenrich]

Steps to reproduce:

1- Create a Customer table in some database

CREATE TABLE [dbo].[Customer](
[Name] [varchar](50) NULL
)

2- type a select statement from Customer table and add some text which causes a syntax error
select * from Customer where Name = 'john' kskskdjd

3- start typing another select in a new line: select * from
4- Hit ctrl-space. The suggestion list doesn't show the customer table or any tables

The new line should not care what was typed in the previous line

[alexa]

This is by design. If we implement such a logic, there will be problems when working with the queries that contain subqueries.

[Thenrich]

Ok.

Another example:
I am in AdventureWorks database. There are several procs whose name start with usp. In a blank query window, I type usp and hit ctrl-space, no suggestions comes up. Why no suggestions for stored procedures?

[Thenrich]

I found out I had to use EXEC first.

[alexa]

Thank you for updating us on this issue. Please let us know if any questions arise.

[HLanger]

I do the same, and probably a lot of people do, many queries in the same document.

IT IS SUPER ANNOYING , the parser should be able to isolate queries in the same document. Or a least there should be a type of document that allow to do that.

[alexa]

I found out I had to use EXEC first.

The parser works on the basis of Finite Automata, therefore it's problematically for it to parse the scripts containing the statements with syntax errors having been not split by ";". Such problems can't be solved by one universal algorithm. You can send us concrete examples of the scripts where suggestions work not correctly for investigation, and, if it's possible, we will fix these issues.

For the same reason (optional separator), it's technically difficult to support the syntax of procedures invocation without the keyword EXEC/EXECUTE, because it will be difficult for the parser to separate from each other even syntactically correct statements.

Please also see https://msdn.microsoft.com/en-us/library/ms177563.aspx where Microsoft says:
Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

I do the same, and probably a lot of people do, many queries in the same document.
IT IS SUPER ANNOYING , the parser should be able to isolate queries in the same document. Or a least there should be a type of document that allow to do that.

Could you please describe this in detail and provide us the full script?