Transact-SQL Debugging Limitations

  • Article

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional

Visual Studio Express
kkyhd4yb.DoesApplybmp(en-us,VS.100).gif kkyhd4yb.DoesApplybmp(en-us,VS.100).gif kkyhd4yb.DoesApplybmp(en-us,VS.100).gif kkyhd4yb.DoesNotApplybmp(en-us,VS.100).gif

There are a number of general limitations to consider when debugging Transact-SQL with the Visual Studio debugger and SQL Server beginning with SQL Server 2005.For more information about debugging Transact-SQL with SQL Server Management Studio, see Using the Transact-SQL Debugger.

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 instance of SQL Server (Transact-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 tier, you can step back and forth between Transact-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, Transact-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 instance of 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 Transact-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 Transact-SQL debugging, you must choose Transact-SQL debugging, and if you want to debug SQL/CLR code, then you must also specify managed debugging.

  • You can do Transact-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 more 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.

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 Transact-SQL interpreter will take effect during the time frame that you are stepping through a Transact-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.

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

See Also

Concepts

Debugging Transact-SQL

Limitations on Debugger Commands and Features

Other Resources

Debugger Security