Vscode-mssql: Go to Definition and Peek Definition not working for stored procedures

[haydnlj]

• MSSQL Extension Version: 1.0.0
• VSCode Version: 1.12.1
• OS Version: Win 10 Enterprise
• SQL Version: 10.50 SP2 - (10.50.4000 MSSQL 2008R2 Service Pack 2)

Note
This was previously posted as issue #776, and closed due to new MSSQL Ext. version release (1.0.0) - rather than a specific fix being applied. Have confirmed via re-testing that the issue has not been addressed with the new version upgrade(s) (VSCode & MSSQL Ext).

Steps to Reproduce:

1. create new SQL file in VSCode
2. connect to existing SQL connection (in this case I'm using 'sa' to ensure permissions aren't an issue)
3. verify connection exists (e.g. execute simple select statement)
4. check definitions return for tables, views, functions
5. attempt to get definition for a stored procedure

Result:
Definitions return fine for all objects except stored procedures.
Stored procedures do open a new file / bring a peek window up, so no errors are presented, but the result is a blank window with none of the object definition included

[kevcunnane]

@haydnlj could you provide a sample script to create a DB we could test against? And also information on the server you're connected to - is it local? On a network? Remote (either Azure or a different network)?

We're struggling to get a reliable repro for this. It may be that queries are timing out more regularly for Stored Procedures since the SMO scripter we use can take a while to fully script out objects. If so, we may need a longer term fix to use different APIs or handle this better. The first step however is to get a good repro, since internally we are able to do this.

[haydnlj]

@kevcunnane - thanks for the follow up. To answer your questions;

The server is on a local network, and although not explicitly mentioned, executing stored procedures via VSCode works perfectly fine.

Based on the quick database I created, the same issue persists - see peek results in image attached, you'll notice that the Proc peek at the bottom returns a blank definition, yet the proc exists fine and can be executed (no special permissions, and was using sa for the experiment).

Scripts below;

---#######SCRIPT 1######################
CREATE DATABASE temp_test
USE temp_test
create table temp_table (id int identity(1,1), random_text varchar(250))
insert into temp_table (random_text)
select 'Hello World';
---#######SCRIPT 1######################

---#######SCRIPT 2######################
USE temp_test
create view temp_view as select * from temp_table
---#######SCRIPT 2######################

---#######SCRIPT 3######################
USE temp_test
create PROC temp_proc (@temp_var int = 0) AS
begin
if @temp_var=0 select * from temp_view
end
---#######SCRIPT 3######################

---#######SCRIPT 4######################
DROP DATABASE temp_test
---#######SCRIPT 4######################

[richardrooks]

I am having this problem on vscode version 1.17.0, mssql version 1.2.0

[gajdosf]

I have this problem too. mssql version 1.2.1 vscode 1.18.0

[abist]

Hey @haydnlj , @richardrooks, @gajdosf , we've made a lot of fixes in the last few months. I wanted to check with you guys whether this problem was fixed for you guys in the latest releases, as we still can't repro this.

[haydnlj]

Seems to be working for me now, thanks - however was only a quick/simple test. Tbh have been using SQL Ops Studio recently as this seemed like the path forward for me (had assumed you were sharing the code base also).

[msftrncs]

Still seems stored procedures and user functions return 'no definition available,

VS CODE 1.23.1
MSSQL 1.3.1

if I put dbo in front, I get a definition for SCHEMA dbo, but not for the user function. It also causes the syntax checker to think there is problems:

In this case the syntax check can be cleared by removing the [] that VSCode automatically generated when I picked the object out of the intellisense. Getting rid of the [] didn't fix the peek problem though, still got the definition for schema dbo.