Visual Studio Hacks [Electronic resources] نسخه متنی

Hack 40. Debug SQL Server

Learn how to use Visual Studio to debug
Transact SQL stored procedures or functions.

SQL statements can be difficult to diagnose and
debug. SQL Server does not include any default way to debug and step
through a stored procedure, but Visual Studio does. Using the Server
Explorer, you can step through the execution of a stored procedure or
function right inside of Visual Studio. The first step is to open the
Server Explorer and create a data connection to your database [Hack #74] .

You will then see the stored procedures and functions of your
database listed in the Server Explorer; these objects for the
Northwind database are shown in Figure 5-21.

Figure 5-21. Server ExplorerNorthwind database

From the Server Explorer, you can right-click on a stored procedure
or function and you will see a menu item named Step Into Stored
Procedure, as shown in Figure 5-22.

Figure 5-22. Step Into Stored Procedure command

When you select Step Into Stored Procedure, you will see the Run
Stored Procedure dialog that is shown in Figure 5-23.

Figure 5-23. Run Stored Procedure dialog

After specifying the values for any parameters the stored procedure
has, click the OK button. Visual Studio will now execute the stored
procedure and open it in the document window, stopping in the first
line of execution. This is shown in Figure 5-24.

Figure 5-24. Debugging a stored procedure

You can now step through the stored procedure as it executes. You can
set breakpoints just as you would in normal codethe only
limitation is that you can specify only location and hit count
breakpoints. The applicable debug windows also work. Figure Figure 5-25 shows an example of the Locals window
displaying the values of the various parameters.

Figure 5-25. Locals window

Because T-SQL is inherently different than .NET languages, the
debugging experience is a little bit different. Here are some of the
limitations with SQL debugging:

You can use only location and hit count breakpoints in T-SQL stored
procedures and functions.

You cannot use Step Into to step from .NET managed code to T-SQL. You
can set breakpoints in the stored procedure though, and the debugger
will break when it comes across them.

You cannot use Break while a SQL statement is already running.

You can't use the Set Next Statement function as you
might in managed code.

Some other differences are the facts that you can't
use the memory or registers windows, as they just
don't apply to SQL. Unfortunately, SQL Print
statements are not shown in the output window either.

You cannot run triggers directly, but you can set
breakpoints in triggers, and if
they are triggered, Visual Studio will break into their execution on
those breakpoints.

5.6.1. Running Remote

You
can
configure Visual Studio and SQL Server to debug stored procedures and
functions that are located on a remote server. Enabling this has
security implications, and I would not recommend it on a production
SQL Server. The process for enabling this feature includes quite a
few steps, which are detailed at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxlrfSettingUpSQLDebugging.asp.

Being able to debug T-SQL stored procedures, functions, and triggers
can be extremely helpful when trying to find errors or follow the
execution of your application.