Debugging SQL Server 2005 Stored Procedures in Visual Studio

Introduction
With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer (see Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer for more information). With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures' statements and have these breakpoints hit when debugging your application.

All editions of SQL Server 2005 include debugging support (including the Express Edition). However, only the Team Systems and Professional editions of Visual Studio enable stored procedure debugging from within the IDE. In short, if you are using Visual Web Developer or Visual Studio Standard Edition then you cannot step through a stored procedure or enter the stored procedure via application debugging.

In this article we will examine how to debug SQL Server 2005 stored procedures through the Visual Studio IDE. We will look at both stepping into a stored procedure directly from the IDE as well as how to set breakpoints within the stored procedure that are then hit when debugging the application. Read on to learn more!

The Different Types of SQL Server Debugging
For Microsoft SQL Server 2005, all database debugging occurs from within the Visual Studio IDE. Database objects like stored procedures, triggers, and User-Defined Functions (UDFs) can be debugged. Visual Studio offers three ways to debug these database objects:

• Direct Database Debugging - from Visual Studio's Server Explorer, right-click on a database object and choose to step into the object. For example, when right-clicking on a stored procedure, the context menu includes a menu option titled "Step Into Stored Procedure."
• Application Debugging - with application debugging you can set breakpoints within a database object. When the associated ASP.NET application is debugged and the database object invoked, Visual Studio's debugger pauses the application's execution as the breakpoint it hit, allowing us to step through the object's statements one at a time.
• Debugging from a SQL Server Project - Visual Studio offers a SQL Server Project type. This project can include both T-SQL and managed database objects and these objects can be debugged by debugging the SQL Server Project itself.

For more information on these three types of SQL Server debugging, see Overview of T-SQL and CLR Debugging in SQL Server 2005.

Another factor that must be taken into account when debugging SQL Server objects is the location of the SQL Server database with respect to the development machine. The database instance being debugged may be either local or remote. A local database instance is one that resides on the same machine as the development machine; a remote one resides on some other computer. Debugging a local database instance requires no extra configuration steps. Debugging a remote instance, however, is more complicated.

This article focuses on Direct Database Debugging and Application Debugging on a local database instance. In particular, we will be debugging a SQL Server 2005 Express Edition database in the App_Data folder; the database and demo web application is available for download at the end of this article. A future article will look at debugging from a SQL Server Project. The sidebar below provides some hints for debugging a remote database instance.

Debugging a Remote Database Instance

The main challenge of debugging a remote database instance lies in ensuring that the Windows account used to launch Visual Studio is the same user account that is used to connect to the database on the remote instance. Moreover, this user account must be a member of the sysadmin role.

For example, you might create an account in the Windows domain named TestDebug. You would then add this user account as a login on the SQL Server and add the account to the sysadmin role. To debug the database objects, you would need to either logon as TestDebug from your workstation, or make sure to launch Visual Studio using the runas.exe command to run it under the context of TestDebug. In either case, once you have Visual Studio up and running under the TestDebug user account, you can start debugging using the techniques we will be discussing in this article.

For a more detailed description of these steps (as well as a ton of other great SQL Server information), check out Bill Vaughn's excellent book Hitchhiker's Guide to Visual Studio and SQL Server, Seventh Edition.

Debugging a Stored Procedure via Direct Database Debugging
Direct Database Debugging is the simplest way to debug a SQL Server 2005 stored procedure. From Visual Studio's IDE you can opt to step into a stored procedure and then step through each of its statements one at a time, inspecting and changing T-SQL variables and parameters within the stored procedure. The download at the end of this article includes a SQL Server 2005 Express Edition version of the Northwind database. I added a stored procedure called DoThings that takes a @CategoryID parameter as input and then displays all products in that category whose price is greater than the average if at least 25% of the products in the category cost more than the average. If less than 25% of the products cost more than the average, then all of the products in the category that cost less than the average are displayed. A silly stored procedure for sure, but it has a number of T-SQL statements and variables and an input parameter, all of which will help illustrate some of the debugging features.

To step into the DoThings stored procedure, navigate to the Server Explorer window, drill down into the stored procedures, right-click on the DoThings node and choose the "Step Into Stored Procedure" option from the context menu. (Note: SQL Server debugging support is only available in the Team Systems and Professional editions of Visual Studio.)

This will start the debugger and execute the stored procedure. Since DoThings expects a @CategoryID value passed in, a dialog box prompts us to provide this value. Enter the value 1 and click OK.

Execution will start at the first statement. You can step from statement-to-statement using the Step Into or Step Over commands (F11 or F10 on the keyboard, respectively), as well as add parameters and variables to the Watch window. The screenshot below shows the stored procedure while being stepped through. The yellow arrow in the margin in the left indicates what statement is currently being executed. The Watch window shows the values and types of @CategoryID and @AvgPrice.

After the stored procedure completes, the results can be viewed through the Output window.

As you can see, Direct Database Debugging is very easy to launch, use, and understand. Simply right-click on a stored procedure from the Server Explorer, choose the "Step Into Stored Procedure" option, and you're off and running.

Debugging a Database Object from a Running Application
Direct Database Debugging makes it easy to debug a stored procedure directly from within the Visual Studio IDE. In some cases, however, we would rather start debugging a database object when it is called from an ASP.NET application. This would allow us to better understand when a particular database object was invoked and its state.

This style of debugging is referred to as Application Debugging. To use this style of debugging we need to perform the following steps:

• Add breakpoints to the database object(s) that you want to debug. A database object will only be debugged if it contains a breakpoint. For example, you cannot "Step Into" a stored procedure from application code that calls the stored procedure. Rather, you must explicitly set a breakpoint within the stored procedure itself.
• Configure the application to debug SQL Server objects. As we will see shortly, this is as simple as checking a checkbox.
• Update the connection string to disable connection pooling. Connection pooling is a performance enhancement that allows an application to connect to a database from an existing pool of connections. This feature, if enabled, does not correctly construct the debugging infrastructure needed on the connection taken from the pool. Since connection pooling is enabled by default, we must update our connection string to disable connection pooling during the timeframe that application debugging is being used. (After you've completed debugging the SQL Server objects via application debugging be sure to reinstate connection pooling.)

Let's tackle each of these steps one at a time.

First, open the DoThings stored procedure in Visual Studio and set a breakpoint on the first statement (DECALRE @AvgPrice money). One task down, two to go!

To configure the ASP.NET application to support SQL Server debugging, right-click on the Project name in the Solution Explorer and choose the Property Pages from the context menu. This will bring up the dialog box shown below. Go to the Start Options tab and check the "SQL Server" checkbox in the Debuggers

section. Two tasks down, one to go!

Lastly, we need to update the connection string used by the application to disable connection pooling. To accomplish this simply tack on the attribute Pooling=false to you existing connection string. Assuming the connection string information is defined within Web.config's <connectionStrings> section, you would update the connection string value like so:

<connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True; Pooling=false" providerName="System.Data.SqlClient"/>
</connectionStrings>

All three tasks have now been completed! To test the application debugging, create an ASP.NET page that invokes the DoThings stored procedure. The demo available for download at the end of this article has such a page. When debugging the ASP.NET application and visiting this page, the breakpoint in the stored procedure is hit and control is delegated to the debugger. Once in the debugger we can step through the stored procedure's statements and view and edit the parameter values and variables through the Watch window, just like with Direct Database Debugging.

Conclusion
Previous versions of SQL Server provided stand-alone tools for debugging stored procedures. With SQL Server 2005, however, stored procedures, triggers, and User-Defined Functions (UDFs) are debugged through Visual Studio Team System or Professional editions. There are three flavors of SQL Server debugging: Direct Database Debugging, Application Debugging, and debugging through SQL Server Projects. In this article we explored the first two types of debugging when working with a local database instance.