Use Visual Studio to set breakpoints for stored procedures in SQL Server and debug the stored procedures.

With Visual Studio's Professional and Team System versions, we can set breakpoints for stored procedures in SQL Server and debug them in stored procedures, in this way, you can debug stored procedures as easily as debugging application code. This tutorial describes two debugging methods for stored procedures: direct database debugging and application debugging.

Introduction

Visual Studio provides a rich debugging experience. With just a few buttons or a few mouse clicks, we can use breakpoints to interrupt program execution and check program status and control processes. In addition to debugging application code, Visual Studio also supports debugging stored procedures in SQL Server. Just as you can set breakpoints in the code file class of ASP. NET or in the class code of the business logic layer, you can also set breakpoints in the stored procedure.

This tutorial will introduce how to enter the stored procedure through Server Explorer in Visual Studio, and how to set breakpoints in it so that we can run ASP.. NET applications can trigger breakpoints when calling stored procedures.

Note:Unfortunately, only Professional and Team Systems versions of Visual Studio can enter and debug the stored procedure. If you are using Visual Web Developer or Visual Studio Standard Edition, you are welcome to continue to read the following to learn the steps required to debug the stored procedure, however, you cannot perform these steps on your own machine.

SQL Server debugging concepts

Microsoft SQL Server 2005 is designed to integrate with the Common Language Runtime (CLR). CLR is the runtime environment used by all. Net assembler programs. Therefore, SQL Server 2005 supports hosting database objects. That is to say, you can create database objects such as stored procedures and user-defined functions (udfs) in the form of methods in the Visual Basic class. This allows stored procedures and udfs to take advantage of the features in. NET Framework and custom classes. Of course, SQL Server 2005 also supports T-SQL database objects.

SQL Server 2005 supports debugging for T-SQL database objects and managed database objects. However, only Visual Studio 2005 professional and team systems can debug these objects. This tutorial details debugging of T-SQL database objects. Debugging of managed database objects will be described in the next tutorial.

A blog post from the SQL Server 2005 CLR Integration Team overview of T-SQL and CLR debugging in SQL Server 2005 focuses on the following three ways to debug SQL Server 2005 objects through Visual Studio:

• Direct database debugging (DDD): We can enter any T-SQL database objects, such as stored procedures and udfs from Server Explorer. Step 1 describes DDD in detail.
• Application debugging: We can set breakpoints in database objects and run ASP. NET applications. When a program runs on a database object containing a breakpoint, the breakpoint is triggered and the program control is transferred to the debugger. Note: when using the application debugging method, we cannot enter the (step into) database object from the application code for debugging, you must directly set a breakpoint in the stored procedure or UDF to be debugged. The debugger will pause at the stored procedure or UDF where the breakpoint is set. The application debugging method starts from step 2.
• SQL ServerProject debugging: Visual Studio Professional and Team Systems both contain an SQL Server Project type, which is commonly used to create hosted database objects. The next tutorial describes how to use the SQL Server Project and debug its content.

Visual Studio can debug stored procedures in both local and remote SQL Server instances. A local SQL server instance is an instance installed on the same machine as Visual Studio. If the currently used SQL Server database is not on your development machine, it is a remote instance. Our tutorial has always been a local SQL server instance. Debugging of stored procedures in a remote SQL server instance requires more configuration steps than debugging stored procedures in a local instance.

If you are using a local SQL Server instance, you can continue from step 1 to the last step of this tutorial. However, if you are using a remote SQL Server instance, during debugging, first, make sure that the Windows User Account you used to log on to the development machine has an SQL Server Logon account on the remote instance. In addition, both the database Logon account and the database Logon account used to connect to the database from the running ASP. NET application must be a member of the sysadmin role. For more information about configuring Visual Studio and SQL Server to debug remote instances, see the debug T-SQL database objects in remote instances section at the end of this tutorial.

Finally, it should be known that the debugging function of the T-SQL database object is not as rich as the debugging function of the. NET application. For example, resumable conditions and resumable filters are not supported, only some debugging windows can be used, Edit and Continue cannot be used, and Immediate windows are rendered useless. For more information, see debugger commands and functional limitations.

Step 1: directly access the Stored Procedure

Through Visual Studio, we can easily directly debug database objects. Let's take a look at how to use the Direct Database Debugging (DDD, Direct Database Debugging) function to enter the Products_SelectByCategoryID stored procedure in the Northwind Database. As the name suggests, Products_SelectByCategoryID is used to return product information of a specific category. It is created in the existing Stored Procedure tutorial for the TableAdapter of the strong-type DataSet. First, go to Server Explorer and expand the Northwind database node. Expand down to the Stored Procedures folder, right-click the Products_SelectByCategoryID Stored Procedure, and select "Step Into Stored Procedure" from the context menu. The debugger starts.

Because the Products_SelectByCategoryID stored procedure is ready to accept the @ CategoryID input parameter, the system will prompt us to enter this value. Enter 1, which will return information about the beverage products.

Figure 1:Input 1 as the @ CategoryID parameter value

After the @ categoryid parameter value is provided, the stored procedure is executed. After the debugger executes the first statement, it stops, rather than until the execution ends. Note the yellow arrow at the border, which indicates the current position in the stored procedure. You can view and edit the parameter values in the watch window or move the cursor over the parameter name in the stored procedure.

Figure 2:The debugger stops at the first statement of the stored procedure.

To skip a stored procedure as a statement, click the step over button in the toolbox or press F10. The products_selectbycategoryid Stored Procedure contains a SELECT statement. Therefore, pressing F10 will skip this statement and complete the execution of this stored procedure. After the stored procedure is executed, its output is displayed in the output window, And the debugger terminates.

Note:T-SQL debugging is performed at the statement level, so you cannot enter the SELECT statement.

Step 2: configure the website for application debugging

Although it is very convenient to directly debug a stored procedure through Server Explorer, we often want to debug it when our ASP. NET application calls the stored procedure. We can add breakpoints to stored procedures from Visual Studio and then start to debug ASP. NET applications. When an application calls a stored procedure with a breakpoint, the execution of the program is paused at the breakpoint. At this time, we can view and change the parameter values of the stored procedure and debug each statement step by step, as we did in step 1.

Before debugging the stored procedure called by an application, we must first instruct the ASP. NET web application to be integrated with the SQL Server debugger. First, right-click the website name (aspnet_data_tutorial_74_vb) in Solution Explorer ). Select the property pages option from the association menu, select the start options entry on the left, and select the SQL Server check box in the debuggers area (see figure 3 ).

Figure 3:Select the SQL Server check box on the property pages of the application

In addition, we also need to update the database connection string used by the application to disable the connection pool. When a database connection is closed, the corresponding SqlConnection object will be put into a pool containing available connections. When you create a database connection, you can retrieve an available connection object from the pool without creating and creating a new connection. This buffer pool function of the connection object improves performance and is enabled by default. However, when debugging, we need to close the connection pool because the debugging infrastructure cannot be correctly rebuilt if the connection obtained from the pool is used.

To disable the connection pool, change the NORTHWNDConnectionString in the Web. config file to include the Pooling = false setting.

 

Copy Code

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

Note:After SQL Server debugging is completed through ASP. NET applications, you must restore the connection pool function by deleting the Pooling settings in the connection string (or setting it to Pooling = true ).

Now the ASP. NET application configuration has been completed, allowing Visual Studio to debug database objects when web applications call SQL Server database objects. The rest is to add a breakpoint to the stored procedure and start debugging!

Step 3: Add and debug a breakpoint

Open the Products_SelectByCategoryID stored procedure and set a breakpoint at the beginning of the SELECT statement. You can click the appropriate position at the border or move the cursor to the start of the SELECT statement and press F9. 4. the breakpoint is displayed as a red circle in the blank area.

Figure 4:Set breakpoints in the Products_SelectByCategoryID Stored Procedure

To debug an SQL database object through a client application, you must configure the database to support application debugging. When you set a breakpoint for the first time, this setting should be automatically enabled. But for the sake of caution, it is best to verify this. Right-click the NORTHWND. MDF node in Server Explorer. The Application Debugging menu item in the association menu should be selected.

Figure 5:Make sure that the Application Debugging option is enabled.

After the breakpoint is set and the Application Debugging option is enabled, You can debug the stored procedure called from the ASP. NET Application. Start Debugger: Open the Debug menu and select Start Debugging, or press F5, or click the green play icon in the toolbar. At this point, the debugger starts and the Website Opens.

The Products_SelectByCategoryID stored procedure is created in the existing Stored Procedure tutorial for the TableAdapter of the strong-type DataSet. Its corresponding web page (~ /AdvancedDAL/ExistingSprocs. aspx) contains a GridView control to display the results returned by this stored procedure. Access the page through a browser. When the page is opened, the breakpoint in the Products_SelectByCategoryID stored procedure is triggered, and the program control is returned to Visual Studio. As in step 1, you can debug each statement of the stored procedure step by step, view and change the parameter values.

Figure 6:The ExistingSprocs. aspx page displays beverage products

Figure 7:Breakpoint for executing the Stored Procedure

In the Watch window in figure 7, we can see that the value of @ CategoryID is 1. This is because the ExistingSprocs. aspx page initially shows the beverage product, and the CategoryID value of this product is 1. Select another category from the drop-down list. This will result in a return and re-execute the Products_SelectByCategoryID stored procedure. The breakpoint is triggered again, but the value of @ CategoryID reflects the CategoryID of the selected drop-down list.

Figure 8:Select another category from the drop-down list

Figure 9:@ CategoryID indicates the category selected on the Web page.

Note:If you access ExistingSprocs. aspx page does not trigger a breakpoint in the Products_SelectByCategoryID stored procedure. Make sure that ASP. the SQL Server check box in the Debuggers area of the Properties Page of the NET Application is selected, the connection pool is disabled, and the Application Debugging option of the database is enabled. If you have any questions, restart Visual Studio and try again.

Debug T-SQL database objects in a remote instance

If the SQL Server database instance is on the same machine as Visual Studio, it is quite simple to debug the database object through Visual Studio. However, if SQL Server and Visual Studio are on different machines, you need to configure them carefully to make all aspects work normally. To this end, we have two important tasks:

• Make sure that the login account used to connect to the database through ADO. NET has the system administrator (sysadmin) identity.
• Make sure that the Windows User Account used by Visual Studio on the development machine is a valid and valid SQL Server Logon account with sysadmin identity.

The first step is relatively simple. First, determine which user account to use to connect to the database from the ASP. NET application, and then add the Logon account to the sysadmin role through SQL Server Management Studio.

The second task requires that the Windows User Account you use when debugging the application is a valid logon account on the remote database. However, sometimes the Windows account you log on to the workstation is not a valid logon account on SQL Server. A better way is to designate a Windows User Account as an SQL Server debugging account than to add your specific Logon account to SQL Server. Then, you can run Visual Studio using the certificate of the Windows Logon account to debug the database objects of the remote SQL Server instance.

The following uses an example to illustrate the above situation. Assume that there is a Windows account named SQLDebug in the Windows domain. You need to add this account to a remote SQL Server instance as a member of a valid logon account and sysadmin role. Then, we need to run Visual Studio as the SQLDebug user to debug a remote SQL Server instance through Visual Studio. Follow these steps: log out from the workstation, log on again with the SQLDebug account, and then start Visual Studio. However, a simpler example is to use our certificate website, and then use runas.exe as the SQLDebug user to start Visual Studio. By using runas.exe, you can use the identity of other user accounts to execute specific applications. To start Visual Studio as a SQLDebug user, run the following command on the command line:

runas.exe /user:SQLDebug "%PROGRAMFILES%\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

For more information about this process, see William R. Vaughn's Hitchhiker's Guide to Visual Studio and SQL Server, Seventh Edition, and how to set SQL Server permissions for debugging.

Note:If your development machine runs Windows XP Service Pack 2, you need to configure Internet Connection Firewall to allow remote debugging. How to enable SQL Server 2005 debugging involves two steps: "(a) on the host machine of Visual Studio, you must add Devenv.exe to the Exceptions list and open TCP port 135; (B) on a remote (SQL) machine, you must open TCP port 135 and add sqlservr.exe to the Exceptions list. If your domain policy requires network communication through IPSec, ports UDP 4500 and UDP 500 must be enabled.

Summary

In addition to debugging the. NET application code, Visual Studio also provides multiple debugging methods for SQL Server 2005. In this tutorial, we have learned two methods: direct database debugging and application debugging. To debug a T-SQL database object directly, first find the object through Server Explorer, right-click the object, and then select "Step... ". The debugger starts and stops the first statement of the database object. In this case, you can debug each statement of the object step by step to view and change the parameter value. In step 1, we use this method to debug the Products_SelectByCategoryID stored procedure.

Application debugging allows you to set breakpoints directly in database objects. When a database object containing breakpoints is called from a client application (such as an ASP. NET web application), the program is suspended and the debugger takes control of the program. The application debugging method is very useful because it clearly shows the application operation that calls specific database objects. However, it requires more configuration and settings than the direct database debugging method.

We can also debug database objects through the SQL Server Project. In the next tutorial, we will see how to use the SQL Server Project to create and debug managed database objects.