SQL Debugging Limitations

You've already seen a few of the limitations of SQL debugging, but here's a more comprehensive list. Most of these limitations are related to the way that SQL works and are therefore not that onerous.

• SQL supports hit count breakpoints, but not conditional expression breakpoints. This is because the debugger is unable to evaluate SQL expressions, as I discussed previously in the first debugging example.

• The Registers and Memory windows aren't supported and therefore can't be used. This is because SQL doesn't support true memory or processor registers.

• You can't use the Set Next Statement command to alter the current execution point or execution sequence. This is because SQL doesn't use the conventional stack frame techniques that a debugger normally exploits to perform this trick.

• You can't step into a stored procedure from managed or unmanaged code. Instead, you need to set a breakpoint within the stored procedure.

• Contrary to popular belief, you can use the Run To Cursor command in the Source window, but you can't use it in the Call Stack window.

• You can't use the Break command to halt a lengthy SQL statement while it's executing.

• The output from a SQL PRINT statement disappears into nowhere as far as the debugger is concerned . It doesn't appear in any place that you might expect if you ever used the Visual InterDev debugger, such as the Debug or Database Output panes of Visual Studio's Output window.

• Any error raised using a RAISERROR statement also disappears as far as the debugger is concerned, being nowhere to be found in the Output window.

• In SQL Server 7.0, you can't see the values of most of the global variables , for instance @@error or @@rowcount . The one global variable that you can view is @@identity . You can overcome this limitation easily using the storage technique recommended previously in the debugging of the sp_DebugExample stored procedure.

• You can't debug triggers directly. Instead, you need the stored procedure to cause the trigger to execute, whereupon you can step into the trigger.

• When SQL Server creates an execution plan for a stored procedure, it often caches variables, such as parameters to the procedure, which it doesn't believe will change over the course of the procedure. If you use the debugger to change the value of a variable that's been cached, SQL Server may well not use the new value, preferring instead to use the value from its cache. After you've changed the value of a variable with the debugger, you should check that the variable really has been set to its new value.

• If you pause on a SQL statement for a significant amount of time, you may see a time-out on the database connection. If the connection is being made using SqlDataAdapter , the debugger will continue despite the time-out, but debugging will end if the connection was made via OleDbDataAdapter . Both SqlDataAdapter and OleDbDataAdapter have a statement time-out that you can set on the Adapter command object. To increase the length of the time-out when using OleDbDataAdapter , you should change the value in the. CommandTimeout property of the OleDbCommand object.

• Debugging a stored procedure may not work more than once if connection pooling is being used, which is the default behavior. Connection pooling attempts to improve performance by keeping old database connections in a pool for potential reuse later. However, if a database connection is reused, SQL debugging is not re-enabled on that connection. To avoid this limitation, you can disable pooling by setting the Pooling switch in the database connection string to false .

Most of these debugging limitations aren't significant, but they're useful to know when one of them suddenly bites you. The really unfortunate limitations are the disappearing output from PRINT and RAISERROR statements, as there's no good way of seeing these values when debugging with Visual Studio.