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

[haydnlj]

• MSSQL Extension Version: 0.3.0
• VSCode Version: 1.10.2
• OS Version: Win8.1 Enterprise
• SQL Version: 10.50 SP2 - (10.50.4000 MSSQL 2008R2 Service Pack 2)

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

[abist]

Hey @haydnlj, thanks for reporting this issue. I'm not able to reproduce the issue on my side. I executed the following -

CREATE PROCEDURE MyProc
AS
BEGIN
SELECT * FROM sys.databases
END

select * from DATABASE_NAME.INFORMATION_SCHEMA.routines

EXEC DATABASE_NAME.dbo.MyProc

Both Go to Definition and Peek Definition work for me. Try running this script and checking whether it still doesn't work for you. If that's the case, it would be really helpful if you give a sample script that fails getting the definition for stored procedures.

[haydnlj]

I tried running what you provided, and didn't change results. That is, the "MyProc" was created, and I could select from ( select * from myDatabase.INFORMATION_SCHEMA.routines ) successfully, and indeed could then execute the proc I had created ("MyProc" not "MyProc2") - either way, executing procedures is not a problem, and all succeed - just definitions are blank via VsCode - all is fine in SSMS.

Sorry for the shaky video attached - can't install screen recording tools. While not shown in video, I did refresh the intellisense cache just prior.

What you'll hopefully see is that definitions can be viewed for views/tables/functions, but not a procedure - the same procedure is able to be executed. This happens to all procedures I try and view definitions for - while not shown in the video, the same session is able to return a result for select OBJECT_DEFINITION(OBJECT_ID('mfe_p_temp')) - confirming this isn't a permission issue.

I did uninstall and reinstall vscode this morning also prior to the test.

compressed.zip

[demyte]

I have this issue as well on extension version v0.3.0

If I connect to an existing database, write a simple exec statement to exec an existing proc then use ALT+F12 on the name of the proc and I either get a tooltip saying no definition found for "procname" even though there are no red squiggly lines under my code, or I get an opened blank peek window.

VS CODE Version
Version 1.11.0-insider
Commit c995ecf2e94a00aa7eda81ed842e787b999e83ae
Date 2017-03-27T06:11:09.027Z
Shell 1.4.6
Renderer 53.0.2785.143
Node 6.5.0

[remindnewmedia]

I've bumped into this. Connection to SQL works fine, I'm able to peek definitions on views and tables but not stored procedures.

I started a SQL Profiler Trace then peek'd a Stored Procedure in the format exec dbo.uspProcName which returned a blank definition window.

The business-end of the SQL Trace seems to be this query, which when run in the same VS Code window returned a table with NULL in the [Text] column and the proc definition in the [Definition] column. Trying the peek proc again still failed.

exec sp_executesql N'SELECT NULL AS [Text], ISNULL(smsp.definition, ssmsp.definition) AS [Definition] FROM sys.all_objects AS sp LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id WHERE (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'[obscuredProcName]',@_msparam_4=N'dbo'

[abist]

We've had new releases and hopefully this is fixed. If you still see this problem in the latest release, please open a new issue so we can track it. Thanks!