How to debug T-SQL with SQL Server Management Studio 2017?

Question

The latest changelog (18.0 Preview 7) of SQL Server Management Studio announced, that the T-SQL Debugger is deprecated.

What are the alternatives for the future? Can someone understand this decision? I fear that removing a fundamental development tool like this will effect many developers.

Hoisch

Not an answer, but you could add to the comments here: feedback.azure.com/forums/908035-sql-server/suggestions/… – Gristmill

I will sure keep my current version safe somewhere. I guess nobody will upgrade to that version... – Meadowlark

Debugging from a Visual Studio database project (SSDT) still works, so that's one alternative. – Radie

@JeroenMostert That might be so, but it also means I have to create such a project everytime i want to debug something... I have no experience with that, can you also debug triggers with this for example ? – Meadowlark

Some people will upgrade, to fully use the new features of SQL Server 2019. However, "The SSMS 18.x installation does not upgrade or replace SSMS versions 17.x or earlier. SSMS 18.x installs side by side with previous versions so both versions are available for use." – Lax

@GuidoG: I have no idea, I just know the feature is there. I never use the debugger. Way back when it was introduced I tried it and found it unreasonably hard to get working on a server, so I just learned to do without it. – Radie

@JeroenMostert So its not such a good alternative afterall then :( – Meadowlark

Though this can be an interesting discussion, this question still is off topic on SO – Meadowlark

@GuidoG: sorry, to clarify: way back when the original T-SQL debugger was introduced I had a hard time getting it working. I make no statements on the quality of the debugger in SSDT, as I don't use that either. I'm fairly certain the SSDT debugger can't debug remote machines, but don't quote me on that. – Radie

@Meadowlark in SSDT you don't need to create a project, you can just connect to SQL Server using server explorer and start debugging. Functionality is exactly the same like in SSMS, but it is annoying to use one more tool with different key shortcuts for same things (like execute query). – Rhineland

I'm sticking with SSMS v17.9 as long as possible (and telling all my devs and customers to as well) and then considering VS.NET debugging once I get a moment to try it out. However, installing VS.NET on a customer server is usually a non-starter and I debug a LOT on customer servers. I'd be willing to pay for a tool too but that might also be hard to get installed. Removing it from SMSS = Big Mistake! – Sphery

Answer 1

You just need to download the Visual Studio 2019 Community.

Once you've done that, create a new project and open the SQL Server Object Explorer (CTRL + S).

You will be able to see your list of SQL Server databases, just as you did in SQL Server Management Studio.

Finally, left click one database and select "New Query". Now you can debug T-SQL just as you did in SSMS.

But the debugger does not work with Azure SQL

Pires

That's a lot of effort to go through (especially creating a new project) just to debug a query. What if it's something that's already in-production? – Writein

@Writein As Piotr mentioned in the comments: "In SSDT you don't need to create a project, you can just connect to SQL Server using server explorer and start debugging." – Seism

you will face still very often with unability to start remote Transact-SQL debugger due to various reasons and permissions, really stupid move to remove debugger, calling one of very essential features deprecated. I believe its better not to use management studio 18 and go back to previous one. Version 18 is dissappointment. – Laborious

This might work well in a dev environment and I do plan to use but, there are times when it has been extremely handy to go to a server, run a debug against a copy of prod and fix. SSMS is usually on the server. Asking a customer to install VS.NET is met with blank stares, gasping or guffaws. Yeah, debugging out on the prod server may be bad practice but sometimes in a pinch you need to take measures fast. Moving data around, setting up a dev env to mimic env is too slow. Having the SSMS debugger was really quite handy and it will be missed by this guy. I wish MS would bring it back. – Sphery

Answer 2

• It seems that Microsoft may have temporarily moved the branch of debugging from SSMS18 to SQLServer Data Tools (SSDT).
• According to developers of DBA Stackexchange community, there is another alternative way to debugging, since Debugger is deprecated in SSMS18.
• Here is the link that shows how to achieve debugging : How to add the Debug button to SSMS v18?

---

ALTERNATIVE: ??

• Just when I thought there would be no solution to this coming out any time soon, to my surprise there might be one.
• There is a tool that I've come across lately while dabbling into this debugger thing in SSMS18 out of curiosity, which goes by the name SQL Complete.
• The company Devart apparently specializes in Database products and provides toolsplug-ins for various major databases.
• Here is a small video of them briefing about the debugging feature in their tool SQL Debugger in the new version of dbForge SQL Complete
• It's available on Visual Studio Marketplace.

Extracurricular

Answer 3

@dens is correct by going to visual studio community edition however this is half of the answer as table variable values cannot be inspected and have the placeholder as (table); This is due to Microsoft not finishing this portion of the debugger. Currently, you can only see primitive data types outputted within the Locals Tab.

The work around to see table variables when they are deleted, updated or inserted into is to utilize the output keyword with each query to output the inserted or deleted elements. Now when you step through you will see the primitive variables within the debugger logger tab called "Locals" and the table variables within the Results or T-SQL tab as you step through. unfortunately the variable name will not be next to the output however as you step through, its pretty clear which table output belongs to which variable

Furthermore, if you are debugging a stored procedure on a SQL database not on your local database, i recommend backing up a local version of the database with the developer edition of SQL server since attaching a debugger to the query will get blocked by the firewall. Then you will require sysadmin privileges and open ports which may work however it did not work within my workplace. we tried even dropping the entire firewall and nothing but good luck.

Unwashed