SQL Debugging Limitations

  • Article

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Standard

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Pro and Team

 Topic applies Topic applies Topic applies Topic applies

Table legend:

Topic applies

Applies
Topic does not apply

Does not apply
Topic applies but command hidden by default

Command or commands hidden by default.

There are a number of general limitations to SQL debugging, which this section describes.

Multi-tier SQL Debugging

  • When debugging multi-tier applications, you cannot use Step Into to step from code in the application tier (C#, Visual Basic, or C+), into the code on the SQL Server 2005 (T-SQL or SQL/CLR). Instead, you set a breakpoint in the stored procedure code, and press Continue (F5) to execute code up to the break point. You can also use Run to Cursor to reach a desired point, without using a breakpoint. Note that inside the SQL Server 2005 tier, you can step back and forth between T-SQL and SQL/CLR code.

  • You also cannot step the other way, from stored procedure code back to the code on the tier that called the stored procedure. If you want to continue debugging after returning to the application tier, set a breakpoint in your application code following the point from which the stored procedure was called.

  • Connection pooling is a technique to improve performance. When an application closes its data connection, a SQL Server connection is not completely closed but is instead kept in a pool that can be reused later if the application subsequently tries to reopen the connection. However, when a connection is reestablished via connection pooling, SQL debugging is not re-enabled.

    You should temporarily disable connection pooling while debugging. To do this, set "Pooling=false" in the connection string used to connect to the SQL Server. When finished with debugging, remove this attribute from the connection string, and pooling will be enabled by default.

  • 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.

    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 SQL debugging, and if you want to debug SQL/CLR code, then you must also specify managed debugging.

  • You can do SQL debugging after attaching to a running application. Note, however, that only those database connections that you create after you have completed the Attach can be debugged. So if an application calls a stored procedure that is taking a very long time, you cannot attach to the connection that called the stored procedure, but only to new connections that call the stored procedure after you have connected to the application.

  • 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 the latest news about MDAC technologies, see the Microsoft Universal Data Access Web site https://www.microsoft.com/data.

Other Limitations

  • Triggers must be fired to be debugged: you cannot debug triggers directly. Instead start debugging in a stored procedure that will cause the trigger to fire.

  • In runtime debugging, a series of subselects (for example, in a union) can fill the netbuffer. This can cause code that runs fine ordinarily to halt during debugging. To get more data, use RecordSet.MoveNext and RecordSet.NextRecordSet.

  • 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.

  • 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 a 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 2005 documentation.

  • You cannot attach to the native SQL Server process while simultaneously debugging a stored procedure.

See Also

Concepts

Debugger Security

Debugging SQL

Limitations on Debugger Commands and Features