27

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?

HappyTown
  • 6,036
  • 8
  • 38
  • 51

4 Answers4

23

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"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jeoffman
  • 1,303
  • 10
  • 23
  • 7
    4 steps when it only took one is 17.9...This. Is. Progress! – Lysoll Dec 19 '20 at 04:07
  • @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? – Error 1004 Apr 22 '21 at 20:00
  • 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? – jeoffman Apr 24 '21 at 10:53
  • 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. – Brett Oct 12 '22 at 23:05
  • i wonder how you might do this with a vm – Mitch Storrie Nov 30 '22 at 04:21
  • that solutions worked for me thanks alot – Mahmoud Elgazzar May 07 '23 at 15:31
7

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.

Lysoll
  • 644
  • 7
  • 4
4

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 !

2

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.

Robert Paulsen
  • 4,935
  • 3
  • 21
  • 27
  • 17
    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. – Aleksei Sep 13 '19 at 07:21
  • 12
    The question was, "How do I debug in SSMS." Not, "What's your personal feelings about step-through debugging in SSMS." – Lysoll Nov 10 '20 at 14:10