How do you debug or step through the code in SQL Server Management Studio 18?

Question

SQL Server Management Studio used to have Debug functionality that would allow to step through the code and watch the values etc. Referring to How to add the Debug button to SSMS v18?, I understand that the functionality is removed from SQL Server Management Studio V18.1.

But what is the alternative now? How do you step through the code to pinpoint a bug in the code?

Freeforall

Answer 1

You can use Visual Studio (the full IDE) to do it:

1. Use "Server Explorer" (Under the View menu) to connect to your DB
2. Right-click the DB and choose "New Query" (or find a stored procedure to debug)
3. Set a break point (F9 key or otherwise) on a line of SQL
4. Right-click inside the SQL editor and choose "Execute with Debugger"

Laconism

4 steps when it only took one is 17.9...This. Is. Progress! – Darell

@marc_s using the above method i manage to debug my queries which are using tables. However, queries which use view a get an error that the object does not exist. Any ideas? – Erichericha

I cannot reproduce your problem - I can SELECT * from views and step through sprocs that use views = Visual Studio 16.9.4. Maybe find the view giving you a problem under Server Explorer, right click and "Show Results" to see if there is something wrong with it? – Laconism

Noting that is not possible if you're coding in an environment where remote debugging is blocked by a firewall. Seems like the only other option is to downgrade. – Adara

i wonder how you might do this with a vm – Lindberg

that solutions worked for me thanks alot – Miki

Answer 2

Your best option is to not upgrade version 18.X, but stay on 17.9 until the Microsoft Product Manager gets sacked. A similar thing happened in going from SQL Server 2000 to SQL Server 2k5. Eventually, they capitulated and re-introduced the feature.

Darell

It's near the end of 2020, I am using 18.8. Still waiting for that manager of your. – Acalia

We're still on 17.9. Don't embolden the product team by installing 18.X. – Darell

It is quite possible to run 17.9 and 18.X on the same machine. I keep both, and use 17.9 for debugging. – Imply

It's July 2023,and still waiting for the feature to come back... – Wilhelminawilhelmine

Make your feedback heard via the community post and the SSMS roadmap blog post. Their team still doesn't quite get the picture that a permanent downgrade of this functionality isn't really acceptable. – Tophus

Answer 3

Ta for answer above, but when trying to debug with Sever Explorer, I find that "SQL Debugging is not supported on Microsoft Azure SQL Database'... ho hum, so I downgrade to SSMS 17.9 or dump the database to local and do it there.

Nice, I just wanted to quickly debug something... by the time I've done that lot I'll have forgotten what the problem I was trying to fix was !

Aright

Answer 4

It's deprecated, so you don't. Personally, I never use the feature and I'm assuming many others didn't either (which is why it's being retired).

The work around is to debug stored procedures by outputting values using PRINT or RAISERROR. Using BEGIN TRAN / ROLLBACK this works well because you can easily run code against the same data.

It's not the same as step through debugging, but it works.

Contrived

That's terrible. It used to be a very convenient way to reproduce deadlocks by running queries step by step in two different tabs. Sad this was deprecated. – Overnight

The question was, "How do I debug in SSMS." Not, "What's your personal feelings about step-through debugging in SSMS." – Darell