|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
SQL Debugging Limitations
The SQL source code debugger provides many common debugging features. SQL debugging supports most debugger commands, such as setting breakpoints and stepping. You can view the values of variables and passed parameters in the Locals window. You can also drag expressions to the Watch window to track them as you step through or run the procedure.
However, some debugger behaviors are not available for SQL debugging. The following limitations apply.
Limitations of Debugger Commands
While debugging SQL, you can use most Visual Studio debugger commands. There are some exceptions:
- The only breakpoints supported are location breakpoints. (Hit counts are also supported.)
- Because SQL does not have true memory or registers, you cannot use the Memory window or Registers window.
- You cannot use Set Next Statement to change the execution sequence.
- You cannot use Edit and Continue.
- You cannot use Step Into to go between T-SQL and managed code (C#, Visual Basic, or Managed Extensions for C++) or native code (C++).
- You cannot attach to the native SQL Server process while simultaneously debugging a stored procedure.
- You cannot use Run to Cursor in the Call Stack window.
- You cannot use Break while a SQL statement is processing.
- The output of SQL PRINT statements does not appear in the debugger or the Database Output pane.
- On SQL Server 7.0 servers, you cannot evaluate global SQL variables.
- You cannot debug triggers directly. Triggers must be fired to be debugged. If the trigger is fired by a stored procedure, you can step into the trigger.
- Cached values are not automatically modified. You cannot always expect that changes to locals or parameters that are cached by the SQL interpreter will take effect during the time frame that you are stepping through an SQL statement. Although you may have modified the value, it may never be checked again. You cannot force a refresh of cached values. Cached values exist because the SQL Server execution plan determines that the values for some variables will not be dynamically loaded for each statement execution or reference. For more information, search for "SHOWPLAN" in the SQL Server documentation.
- A managed application can connect to a SQL Server data source using the .NET Framework Data Provider for SQL Server, which gives better performance than connecting with OLE DB or ODBC. You can do both managed debugging and SQL debugging in the same debugger session. However, if a managed application is running and you attach to the application using the debugger, you are given the choice of what kind of debugging you want to do. If you want to do SQL debugging, you must choose both managed and SQL debugging.
- If you are debugging through a connection made with OleDbDataAdapter, waiting for a significant time after hitting a breakpoint will cause the connection to time out. When you try to continue debugging after this timeout (choosing Continue from the Debug menu, for example), the debugger will quit (instead of continuing execution). This is expected behavior. The debugger quits because OleDbDataAdapter, unlike SqlDataAdapter, does not throw an exception when a timeout occurs. To work around this problem, set the timeout value to a high number when using OleDbDataAdapter.
For information on setting the timeout value for .NET Framework data providers, see OleDbCommand.CommandTimeout Property and SqlCommand.CommandTimeout Property in the .NET Framework Class Library documentation.
For information on setting the timeout value for native OLE DB, see Connection String Syntax in the OLE DB Programmer's Reference.
For information on setting the timeout value for native ODBC, see SQLSetConnectionAttr in the ODBC Programmer's Reference.
For the latest news about MDAC technologies, see the Microsoft Universal Data Access Web site (http://www.microsoft.com/data).
- Debugging a stored procedure called from native or managed code may not work more than once if connection pooling is enabled. Connection pooling is a technique to improve application performance. When an application closes its data connection, a SQL Server connection is not completely closed but instead kept in a pool that can be reused if the application subsequently tries to reopen the connection. However, when a connection is reestablished via connection pooling, SQL debugging is not re-enabled.
Stored Procedure Names that Contain Quotes
If the name of a stored procedure contains quotation marks, you may get a debugger error message. For more information, see Error when Debugging Procedures with Names that Contain Quotes.