Visual Studio is a great tool for developers, and it offers so many great features, such as IntelliSense, unit testing and code analysis. One of the great and very useful features is debugging. It is possible to debug managed code and do things like adding breakpoints, stepping into modules and watching values of variables and objects.
But it is less known that it is also possible to debug stored procedures from Visual Studio with almost the same functionality. The problem is that it doesn’t work out of the box, and there are a few steps you should take (only once) in order to enable debugging of stored procedures from your Visual Studio project.
The goal of this post is to list these steps and get you up and running as quickly as possible, so that you can seamlessly debug your managed code and your stored procedures by stepping from one to another.
I’m using Visual Studio 2013 and SQL Server 2014 for the purpose of this post. If you are using different versions, then some of the instructions in this post might be a bit different for your environment, but the general idea is the same. If you follow the instructions in this post, it shouldn’t be too hard to apply them to your environment as well.
Ready? Let’s go…
First, make sure that you have the SQL Server Database Tooling installed. With this tooling you can create SQL Server projects in Visual Studio and leverage a lot of great functionality, such as schema and data compare, database unit testing and code refactoring. In Visual Studio 2013, go to Tools -> Extensions and Updates, and locate it under “Product Updates”.
Click on “Update”, and follow the instructions. Once the setup is complete, you’ll be able to create SQL Server projects in Visual Studio, develop managed code and stored procedures in the same environment, and leverage so many great features. Have fun!
If you already have the database tooling installed, it might not be the latest version, and it might not support SQL Server 2014. If you open SQL Server Object Explorer and try to create a new SQL Server connection, and the “Connect to Server” dialog looks like this:
As you can see, this dialog includes the logo of SQL Server 2012, which means it’s not even aware that there is a newer version, because it’s too old. It uses the SQL Server native client for SQL Server 2012, and it doesn’t support SQL Server 2014. If you try to continue and connect to a SQL Server 2014 instance, you’ll get the following error message:
In this case, install the latest update of the database tooling from the “Updates and Extensions” window, as described earlier.
Now, if you try to add a SQL Server connection again, you’ll see the following dialog:
Much better! Now you can connect and work with SQL Server 2014 from Visual Studio. Congratulations!
Let’s move on…
Now, I’m going to create a sample database with a sample stored procedure. Here is the script:
USE master; GO CREATE DATABASE DebugTest; GO USE DebugTest; GO CREATE PROCEDURE dbo.DoStuff ( @InputParameter AS INT , @OutputParameter AS INT OUTPUT ) AS PRINT N'Beginning Execution...'; SELECT ObjectId = object_id , ObjectName = name FROM sys.objects; SET @OutputParameter = @InputParameter * 2; PRINT N'Ending Execution...'; GO