How to debug stored procedure on Microsoft SQL Server 2017?

Question

I need to debug stored procedure on the local SQL Server using data stored in this server.

I have on my device:

• Microsoft SQL Server 2017 Standard
• Microsoft SQL Server Management Studio 2014
• Microsoft SQL Server Management Studio 18.0 Preview 5
• Visual Studio 2017 Professional

Debugger was removed from SSMS 17.9 and newer.

Attempt 1. Debug usign SSMS 2014. I got following error messages:

Unable to start program 'MSSQL:://localhost/MyDB/sys/=0'.

Same error for stored procedures that have no parameters.

Attempt 2. Debug using Visual Studio.

I can execute stored procedures and even change them on the SQL server side via VS.

But similar errors when I try to debug:

Failed to start debugger. An exception was generated: 'Data is Null. This method or property cannot be called on Null values.'

Unable to start program 'MSSQL:://localhost/?/sys/=0'. Operation not supported. Unknown error: 0x80004005.

So how am I supposed to debug stored procedures in Microsoft SQL Server 2017?

Or am I doing something wrong?

Answer 1

You don't have to go all the way back to SSMS 2014. SSMS 17.8.1 is still available for download.

Or you can use SQL Server Data Tools in Visual Studio to debug.

How to: Debug Stored Procedures