SQL Server debugging in .NET 1.x

Following on from my tips on remote debugging in .NET, here are some tips on SQL Server debugging. Once again, please note that these tips haven't been validated against .NET 2.0 or VS2005.

Perhaps the first important tip is that it almost never advisable to debug a production database server. Single-stepping through a stored procedure can cause the debugger to lock system resources that are needed by other SQL Server processes, which could cause nasty production problems.

SQL Server version and compatibility requirements

• The database must be SQL Server 6.5 (SP3 or later), SQL Server 7.0, or SQL Server 2000.
• The database server must be running Windows NT 4.0 or later.
• The application that you're debugging must be using the managed data adapter for SQL Server.
• You must be using OLE DB, ODBC, or DB-LIB as the database connection protocol. 
• The desktop edition of SQL Server 2000 can be debugged using the Professional version (or higher) of Visual Studio .NET.
• If you can't see stored procedures in Visual Studio’s Server Explorer window, it’s possible that you're trying to debug the full edition of SQL Server with only the Professional version of Visual Studio — this won't work.
• Although Query Analyzer 2000 has a new built-in debugging feature, which incidentally works when used with either SQL Server 7.0 or SQL Server 2000, Query Analyzer 7.0 doesn't have this feature.

SQL Server debugging components

If you're having trouble setting up the SQL debugging components, you should check for the existence of the following files:

• Sqlle.dll and sqldbreg2.exe must exist on your debugging workstation. The former library is the SQL language engine for the debugger.
• Sqldbg.dll must exist on both the database server and the debugging workstation. This library is the SQL debugging proxy.
• Mssdi98.dll must exist in the \binn sub folder of every SQL Server instance on the database server.

If you're running the full version of SQL Server, Visual Studio installs a version of mssdi98.dll in the appropriate location, correctly overwriting the version that SQL Server installs by default. If you're using the desktop edition of SQL Server 2000 together with the Professional version of Visual Studio, the Professional edition of Visual Studio doesn't install mssdi98.dll in the correct folder. Instead, you need to copy the library manually to the \binn folder of every SQL Server instance on the database server. In addition, if Visual Studio isn't installed on the same machine as the desktop edition of SQL Server, you should copy sqldbg.dll to the following folder on the SQL Server machine:

\Program Files\Common Files\Microsoft Shared\SQL Debugging

Once you've copied this library to the correct location, you need to register it with the following command at the command line:

regsvr32.exe sqldbg.dll

Permissions and the sp_sdidebug stored procedure

Any SQL Server user account involved during SQL debugging must have “execute” rights on an extended stored procedure called sp_sdidebug, a right that only the system administrator account (sa) has by default. This extended stored procedure seems to be an application programming interface (API) to the previously mentioned mssdi98.dll, though I can't find any publicly available documentation for it. The sdi in the stored procedure’s name stands for SQL debugging interface.

Whether the SQL Server account belongs to a developer who is debugging via Visual Studio’s Server Explorer window or using Query Analyzer, or the account is the one specified in the SQL Server connection string used by an application, you need to establish that this account is permitted to execute sp_sdidebug. To check this, use the account to log onto the SQL Server and then type the following SQL command using Query Analyzer or any other tool of your choice:

EXEC master..sp_sdidebug

You'll see either a result stating that the command completed successfully or an execute permission error. If you see the latter result, you should also check that the account has permission to the master database itself. It’s not unknown for a DBA to give permission to the stored procedure, but not to the master database. The quickest way to grant execution rights for a SQL Server account to sp_sdidebug is to enter the following SQL using a tool such as Query Analyzer:

GRANT EXECUTE
ON sp_sdidebug
TO SpecificAccountName

Another permission issue with sp_sdidebug can occur with SQL Server running under Windows NT 4.0. Some NT 4.0 configurations have the SQL Server service set to run using the System account, which doesn't have permission to execute the sp_sdidebug stored procedure. To make this work, you can change the user that runs the SQL Server service. You can do this by going to the service configuration screen, clicking the Start button, and selecting Settings ➢ Control Panel ➢ Services ➢ MSSQLServer ➢ Properties. Then go to the Startup section and change the Logon account from System to a specified user account that you know is allowed to execute the aforementioned stored procedure. Once again, this workaround is only necessary if you have SQL Server running under Windows NT 4.0.

Finally, if you've installed SQL Server 2000 SP3, then you'll encounter a new security switch that was added as a part of SP3. This switch only affects SQL debugging from a client application, not SQL debugging directly from Visual Studio. To gain permission for SQL debugging with SP3, you have to execute the following additional command:

EXEC master..sp_sdidebug ‘legacy_on’

Unfortunately, this setting isn't persistent, so you'll need to re-execute the command if you reboot your SQL Server.

SQL server debugging restrictions

Here's a reasonably comprehensive list of SQL Server debugging restrictions. 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.
• 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 un-managed 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'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.