Vscode-mssql: Intellisense - DB Information

[nivek7]

• MSSQL Extension Version: 1.3.0
• VSCode Version: 1.2.1
• OS Version: 10.13.3 Mac OS High Sierra

My environment is that I am connecting to a database across a VPN connection so I appreciate that that this maybe not the typical environment. I seem to recall that this was not always an issue for me, but it has been an issue for quite some time now. I am hoping that there is a setting or something that I am not aware of that can remedy this issue.

Intellisense for database objects does not seem to respond when I am working on existing code.

New code:
1 - I make a new connection to the database and once that connection is active, I see that Intellisense is "updating". This seems to complete in under 15 seconds.

2 - If I then start typing a new database query, Intellisense shows database object hints as expected.

Existing Code: (Stored Procedures)
1 - I open an existing Stored Procedure from the File Explorer and I am prompted to choose a data connection.

2 - I choose a connection from my list of saved connections, and once that connection is active, I see that Intellisense is "updating". Again, this seems to complete in under 15 seconds.

However, Intellisense does not seem to provide DB hints for any changes that I make to the stored procedure code. I see reserved words, but no table and column name hints.

For example, if I try and insert a column into the SELECT section of my stored procedure and I type in the table alias and period character, all I see are reserved words, no database hints.

Example code (I am inserting a new line directly after "SELECT" and type "au.", I get nothing but DB reserved words):
SELECT
au.
, im.item_id [Item ID]
, au.column_changed [Column Changed]
, au.old_value [Old Value]
, au.new_value [New Value]
, CONVERT(VARCHAR(10), au.date_created, 101) [Date]
, au.created_by [Created By]
, COALESCE(au.key2_value,'') [Location ID]

FROM audit_trail au WITH (NOLOCK)

[kevcunnane]

This is an interesting one. Can you show how the stored procedure is defined? Does it work with Alter but not a Create statement?

[nivek7]

It occurs on both CREATE and ALTER. Pretty much as soon as I am "in" a stored procedure block, Intellisense stop functioning, but it is sporadic, after some time it may come back

Just as a test:
1 - I started VS Code

2 - Since I just started VS Code, this tab was not connected to a datasource, so I connected to one.

3 - I waited for Intellisense to update.

4 - Once it finished updating, I started entering a new simple SELECT:

SELECT oh.order_no
FROM oe_hdr oh WITH (NOLOCK)

** Intellisense worked as expected

5 - I then navigated to the top of the code and inserted a CREATE PROC...
My code looked like this:
CREATE PROC KL_TEST
AS
SELECT oh.order_no
FROM oe_hdr oh WITH (NOLOCK)

6 - I tried entering another column

** Intellisense stopped functioning with table/column hints

7 - I let it "sit" for a couple minutes, Intellisense started working again

However, I have let it sit for much longer and Intellisense doesn't show table/column hints.

I have also seen this occur with other "container blocks" like inline functions:
COALESCE(...)
NOT IN(...)

Intellisense seems to freak out, for a period of time when SQL code is contained in a block like Functions, Stored Procedures, Inline Functions etc.

It hasn't always been this way, but it would seem once Intellisense has updated and it locally cached, that I shouldn't have to wait minutes for it to supply table/column hints. It seems to populate reserved word type hints much more consistently, but ironically, those are the hints that I have the least use for.

[LillyBeast]

Plus one on this, and, in general, my intellisense does not work for DB info on any previously saved file.
I.e., works when right-click-create new query from server connection browser, but stops working if I save/open that query.