How to Debug Stored Procedures in SQL Server using Visual Studio 2019

In this quick tutorial, we’ll discuss how to debug SQL queries written in Stored Procedures using SQL Data Tools in Visual Studio 2019. When developing complex Stored Procedures, sometimes a need for some Debugger tools feels out. Before the latest version of SQL Server Management Studio version 18, there was a Debug option available. Using…

By.

•

min read

In this quick tutorial, we’ll discuss how to debug SQL queries written in Stored Procedures using SQL Data Tools in Visual Studio 2019.

When developing complex Stored Procedures, sometimes a need for some Debugger tools feels out. Before the latest version of SQL Server Management Studio version 18, there was a Debug option available. Using which we can easily add breakpoints to easily debug SQL queries and watch variables.

 

But with the release of latest version 18 of SQL Server Management Studio (SSMS), the Debug option is now removed

 

Solution

They have removed this option, as we can easily connect to SQL Server from Visual Studio and perform Debugging on Stored Procedures after connecting the server.

Let’s check how to enable debugging in Visual Studio.

 

Step 1:

Go to start menu and search for Visual Studio Installer and click on it. Or otherwise, you can also click on Tools>> Get Tools and Features…

 

Step 2:

After opening the Visual Studio Installer, under the Workloads check the Data storage and processing. Then click on Modify. This needs to close all other instances of Visual Studio IDE.

 

Step 3:

After completing the installation open the Visual Studio, then click on Tools then Connect to Server…

 

Step 4:

After connecting to the Server, click on SQL Server Object Explorer to see your Databases.

 

Step 5:

To start to debugging, go to the Procedure you want to debug, then right-click then select Debug Procedure… Then it will enter into debugging mode.

 

 

 

That’s all for enabling and performing Debugging in Visual Studio 2019. Debugging the complex code from existing code can help in better understanding the logic used in comparatively less time.

5 responses to “How to Debug Stored Procedures in SQL Server using Visual Studio 2019”

  1. stachu99 Avatar
    stachu99

    •

    Does anyone know how to watch table data under debugging sql statements?
    Example: In a trigger after update are logic tables Inserted, Deleted. How watch records of those tables during debugging?

    Tip
    Debugging a trigger: Create a procedure with statement firing the trigger. Debug the procedure.

  2. Robert RACKL Avatar
    Robert RACKL

    •

    I can follow everything up to and including step 4. Right-clicking in step 5 on a stored procedure does not offer the debug options described here. I only get “Add New Stored Procedure”, “Open”, “Execute”, “Copy”, “Delete”, “Refresh”, “Properties”. What could be amiss? It behaves the same as VS 2017.

    1. earwig Avatar
      earwig

      •

      I had this same problem, but it turned out I wasn’t following the instructions precisely. Instead of using SQL Server Object Explorer, I was just using Server Explorer/Data Connections. While you can see the stored procedures in both places, only the Object Explorer supports debugging. If your stored procedures show padlocks, you’re in the wrong place.

      1. Robert RACKL Avatar
        Robert RACKL

        •

        Thank you for that tip! That was exactly my problem.

    2. meera Avatar
      meera

      •

      unable to access sql server debugging interface. Access is denied.Cannot connect to visual studio remote debbugger on remote computer

Leave a Reply

Your email address will not be published. Required fields are marked *