Debugging Stored Procedures in Visual Studio 2010

When business applications with database back ends don't work as expected, the problem can lie with the application, the database, or something in between. Most application developers are familiar with using the debugger built into Visual Studio to debug application logic. However, you can use Visual Studio 2010 to debug SQL Server stored procedures as well. This feature isn't new to Visual Studio 2010. However, as with most capabilities, it has evolved over the years.

Related: Debugging (Nested) Stored Procedures with SQL Server Profiler

The advantage of using Visual Studio rather than SQL Server Management Studio (SSMS) to debug stored procedures is the ability to run them in the context of the application. Doing so lets you see whether the parameters are being assigned correctly and see how SQL Server is processing those parameters when it's running the stored procedure code.

I'll show you how to leverage the debugging capabilities within SQL Server 2008 R2 when working with an application in Visual Studio 2010. Specifically, I'll discuss how to configure debugging in SQL Server, then show you how to configure and debug SQL Server stored procedures from Visual Studio 2010. I'm assuming you have at least minimal familiarity with both SSMS in SQL Server 2008 R2 and Visual Studio 2010.

By definition, debugging is something that's done in a development environment. Although reproducing an error can be done in a production environment, the steps I'm about to show you—in particular those related to enabling a database to allow debugging—should never be done using a production database.

Configuring SQL Server 2008 R2

In my development environment, I'm running SQL Server 2008 R2 and have installed the publicly available AdventureWorks database. I'll be using this database as the target for the stored procedure being debugged. However, to provide some semblance of how your environment will work, I'll look at debugging from a remote machine. Although many organizations have a local database to debug against, this isn't feasible in some organizations.

You might wonder why I'm configuring the debugging capabilities in SQL Server first. The simple answer is that if you start within Visual Studio and there's a configuration setting or firewall port blocked, you can waste quite a bit of time trying to debug Visual Studio's configuration and connection. By starting with SQL Server, you'll know that the built-in debugging capabilities exposed through SSMS are operating as expected.

On the SQL Server side, there are really only two items to configure: the local Windows Firewall (or any other machine's local firewall) and the account permissions. Let's start with how to configure the local Windows Firewall. Note that I won't walk you through opening the standard 1433 and 1434 ports because they're opened by default on any network-accessible SQL Server instance. Nor will I walk you through configuring the additional ports required for debugging, because you can leverage SSMS to automatically open and configure them for you.

Be certain you're using an account that has systems administrator privileges on your local test machine running SQL Server. To begin, start SSMS using the Run as administrator option in Windows. After you've opened SSMS, connect to SQL Server using the machine's name. Don't use local or 127.0.0.1. Using the machine's name in your connection causes the database engine to behave as it does when the connection is remote.

Next, access the AdventureWorks database, opening the Programmability and Stored Procedures nodes. For this example, select the uspGetManagerEmployees stored procedure.

Execute the uspGetManagerEmployees stored procedure using the integer value of 109 as your sample manager ID for the stored procedure. SSMS will return a set of results in a new query window. At the top of the window, you'll see some T-SQL statements that were used to set your parameter and execute the stored procedure. On the left side of the code window, click the gray bar next to one of the T-SQL statements to add a breakpoint to the T-SQL code. Keep in mind you aren't setting a breakpoint in the stored procedure at this point but rather in some boilerplate T-SQL code generated to call the stored procedure.

SSMS will automatically highlight the selected line and add a red dot to indicate that a breakpoint has been set. Now, click the Debug button on the toolbar or press F5 to start the debugger.

Starting the debugger on a SQL Server system not fully configured for remote debugging will cause an error dialog box to immediately appear. As Figure 1 shows, this dialog box is extremely helpful because it doesn't just tell you that your firewall isn't configured for debugging but actually provides guidance by explaining which ports need to be opened. More important, it offers you two automatic configuration options that can save you from having to manually open the ports and configure the firewall. Thus, unless your development environment crosses subnets, you can select the second radio button and allow SQL Server to update your firewall settings to allow only those computers on the local subnet to connect for debugging. The advantage of this setting is that it limits your exposure to others on the same small branch of the company network. Note that if you didn't start SSMS with an administrator account, the automated update to your firewall settings will fail.

Figure 1: SSMS's Error Dialog Box Indicating that the SQL Server System Isn't Fully Configured for Remote Debugging

The local Windows Firewall is now configured, so it's time to work on the account permissions. You need to connect to the database using an account that has systems administrator privileges. However, this is actually a dual requirement.

When debugging, you're going to be using the account specified in the application's connection string to connect to the database server. In most applications, the connection string specifies an account that has production rights only. However, for the debugging function to work, the account must be granted systems administrator rights on the database server. Although you might be tempted, don't grant systems administrator rights to the existing account specified in the application's connection string. Instead, change the application's connection string so that it points to an account with systems administrator rights. After you're done debugging, you can change the application's connection string back to the original one.

The account used in the connection string is only half of the permission requirement. When debugging, you're also going to be using your Windows account and the permissions it has on the database server. Because you're connecting to a remote server, this almost certainly means that you're using a domain account. The fact that you're an administrator locally means nothing on your database server. Instead, you need to make sure that your domain account is set up with systems administrator rights on the server (and not just the database) that you want to debug. It also means that while debugging, you might want to consider using integrated security so that you only need to configure a single account.

This is essentially all you need to do to get debugging enabled. Now it's time to connect using Visual Studio 2010.

Configuring Visual Studio 2010

Visual Studio 2010's Server Explorer will be the starting point for configuring and using Visual Studio 2010 to debug a stored procedure. Server Explorer is a specialized window within Visual Studio. If it isn't currently open within your Visual Studio 2010 display, select Server Explorer on the View menu to open it. Figure 2 shows the top section of Server Explorer. By default, the top item is the Data Connections node. To debug a stored procedure from within Visual Studio, your database must be defined as a member of this node.

Figure 2: The Database Connections Node in Server Explorer

It's important to note that while you've made sure that you can debug from within SSMS, the actual debugging from within Visual Studio bypasses SSMS and works directly with the database server. Because you'll be debugging the stored procedure within Visual Studio, you need to define a connection to the database server to allow the Visual Studio debugger access the database. Accordingly, it's a good place to test debugging against your database connection.

As Figure 2 shows, my desktop already has the connection information defined for the AdventureWorks database. If you need to set up a database connection, right-click the Data Connections node and select Add Connection. In the Add Connection dialog box, provide the requested server information. Note that I typically debug using my Windows account and use those credentials to connect to the database server. However, keep in mind that the account you use must have administrator rights on the database server.

After you define your connection, it'll be displayed under the Data Connections node and you can expand it, as shown in Figure 2. Then expand the Stored Procedures node, which contains all the stored procedures available in the AdventureWorks database. Right-click the uspGetManagerEmployees stored procedure and select the Step Into Stored Procedure option.

At this point, if something isn't configured correctly, you might see an error dialog box. For example, if you didn't configure the firewall settings correctly, an error dialog box like the one in Figure 1 might appear. Like SSMS's error dialog box, Visual Studio's error dialog box lets you automatically update your firewall. However, unlike SSMS, Visual Studio provides a second dialog box if you tell Visual Studio to configure the firewall but you're not currently running Visual Studio with administrator rights. As Figure 3 shows, this dialog box notes that elevated permissions are required to configure the firewall and asks if you want to continue. If you click Yes, Visual Studio will automatically start a second instance with administrator permissions. Using this second instance, Visual Studio will update your firewall settings on the machine you're using.

Figure 3: Visual Studio's Dialog Box Noting that Elevated Permissions Are Required to Configure the Firewall

If everything is configured correctly when you select the Step Into Stored Procedure option, the dialog box shown in Figure 4 is displayed. This dialog box lets you manually enter the parameters required to run the selected stored procedure. Enter 109 for the @ManagerID parameter and click OK. Clicking OK starts the debugger and opens the selected stored procedure in Visual Studio.

Figure 4: Manually Entering the Parameter Required to Run the uspGetManagerEmployees Stored Procedure

Figure 5 shows the uspGetManagerEmployees stored procedure within Visual Studio's text editor. Notice the yellow arrow and red dot. The yellow arrow indicates where the debugger is sitting. From within the debugger, you have access to the Locals window and can verify locally declared variables in your T-SQL code, such as the @ManagerID parameter in this case. The red dot indicates a breakpoint, which I added after this window was open. Adding a breakpoint in Visual Studio 2010 is similar to adding one in SSMS. On the left side of the code window, click the gray bar next to any line of code (except for comments).

Figure 5: The uspGetManagerEmployees Stored Procedure in Visual Studio's Text Editor

Debugging a Stored Procedure

The next step is to debug a stored procedure from within an application in Visual Studio 2010. For this purpose, I created a simple console application named DebugStoredProc, which you can download by clicking the link at the top of this page. This application connects to the AdventureWorks database and calls the uspGetManagerEmployees stored procedure to retrieve a list of the direct and indirect employees of the specified manager. For test purposes, we'll be using the manager whose ID is 109.

After you download and compile the application, open the Module1.vb file and find the Main subroutine shown in Listing 1. Set a breakpoint on the line of code highlighted in callout A. This code populates a DataSet object with the results of the stored procedure's query.

Sub Main()  Dim ds As DataSet = New DataSet()  Using conn = New SqlConnection(    "Data Source=DadsDesktop;Initial Catalog=AdventureWorks;Integrated Security=SSPI")    Try  conn.Open()  Dim cmd = New SqlCommand("uspGetManagerEmployees", conn)  cmd.CommandType = CommandType.StoredProcedure  cmd.Parameters.AddWithValue("@ManagerID", 109)  Using da = New SqlDataAdapter(cmd)// BEGIN CALLOUT A    da.Fill(ds)// END CALLOUT A  End Using  If ds.Tables.Count > 0 Then    If ds.Tables(0).Rows.Count > 1 Then      Console.WriteLine(ds.Tables(0).Rows(0).Item(0))      Console.ReadKey()    End If  End If    Catch ex As Exception  Console.WriteLine(ex.Message)  Console.ReadKey()    End Try  End UsingEnd Sub

Next, you need to set a breakpoint in the uspGetManagerEmployees stored procedure. To do so, open a valid connection to the AdventureWorks database in Server Explorer, following the instructions given in the previous section. (If you don't open the connection in Server Explorer before attempting to step into your stored procedure, the Visual Studio debugger won't see the connection information and will step over your database call.) Figure 5 shows the line at which to set the breakpoint.

Finally, you need to enable the SQL Server debug engine. When Visual Studio starts debugging an application, it actually has the option to run one or more debug engines. By default, the engine used to connect to SQL Server and step into stored procedures isn't enabled. To enable this debug engine for the application you're currently working on, right-click the project name (in this case, DebugStoredProc) in Solution Explorer and select Properties.

In the project's properties window, select Debug* in the list on the left, as shown in Figure 6. At the bottom of the window, you'll see a group of check boxes that let you enable one or more of the available debug engines. Select the Enable SQL Server debugging check box. (Leave the Enable the Visual Studio hosting process check box selected.)

Figure 6: Enabling the SQL Server Debug Engine

After you've enabled the SQL Server debug engine, you can start debugging your application. Assuming you set a valid breakpoint in the Main subroutine, the sample application will stop just prior to calling the uspGetManagerEmployees stored procedure, at which point you can verify your parameter and connection string information before stepping into the database.

Once you're in the uspGetManagerEmployees stored procedure, you can step through the top-level commands and make sure that the stored procedure is behaving as expected. While debugging, you can see the locally declared variables, just as you would in your code. However, unlike debugging code, you're unable to modify these variables within SQL Server. There are other limitations as well. For example, you can't edit and continue your T-SQL code within the debugger. However, the core debug capabilities that let you walk through your code and see how it's behaving are fully supported.

A Powerful Capability

Visual Studio's integration with databases through Server Explorer provides you with an effective way to debug data applications from within a single environment. Debugging a complex stored procedure, especially one with many parameters, from within the context of the application calling it is a powerful capability. Using Visual Studio to debug stored procedures is a feature well worth exploring if you need to have more insight about how a stored procedure is behaving in the context of your application.