Azure SQL Managed Instance T-SQL query debugging

Question

When trying to debug a T-SQL query within Azure SQL Managed Instance database from Visual Studio, I get an error:

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

Followed by this:

Unable to start program MSSQL Operation not supported Unknown error 0x80004005

Debugging a T-SQL query from within Visual Studio 2019 current version 16.11.11 yields the following errors:

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

Unable to start program MSSQL Operation not supported Unknown error 0x80004005

Answer 1

Azure SQL Managed Instances cannot be debugged using the debug method of SSMS or via Visual studio. Answer by the MS support team : " As discussed Azure SQL Managed Instance does not support debugging as SQL Server engine DDL’s which is required by the debugger are not accessible in PaaS environment."

On all Azure SQL PaaS database options (DTU model, vCore model, Serverless, Managed Instance, Hyperscale) and Synapse you will have that constraint. Even tools like SQL Server Management Tools (SSMS) that once had a Debugger integrated it no longer has it, and honestly it was never useful when I had to use it.

Azure Data Studio has a Profiler Extension that help a little bit to determine the sequence of instructions that were executed to produce a result set but won't help you to watch the values of variables.

If all this is painful for you maybe your organization should consider choose a SQL Server VM (IaaS) instead of PaaS.

You can also export Azure SQL Databases to a local developer environment and do debugging locally.

Finally, if you need to debug on Production use PRINT or SELECT statements of T-SQL to watch the values of variables or display intermediate result sets. You can also save intermediate results or outputs into a temporary table or log table.