Overview of T-SQL and CLR debugging in SQL Server 2005


SQL Server 2005 ships with a new debugging engine that supports debugging of any T-SQL and CLR code running in the server, including batches, stored procedures, user defined functions/aggregates/triggers, etc.  You can use Visual Studio 2005 to debug against SQL Server 2005 or SQL Server 2000, but you can not use Visual Studio 2003 or earlier to debug against SQL Server 2005 because the debugging engine is not compatible.


 


There are some improvements in debugging in SQL Server 2005:


. Its much easier to setup than debugging in SQL Server 2000.  You can enable debugging SQL Server 2005 from Visual Studio 2005 by following these simple steps (please note that Remote Debugging Monitor mentioned in the steps is not required for T-SQL debugging).


. Integration of T-SQL debugging and CLR debugging.  You can step in/out from T-SQL code to CLR code, or vise versa.  You will get mixed call stack consisting of T-SQL frames and CLR frames, and inspect T-SQL and CLR variables on any frame.


. Full functionality of CLR debugging.


. Isolation of database connections in T-SQL debugging.  When you break in T-SQL code in one connection, other connections are not suspended and can continue to run normally until they require resource locked by the connection being debugged.  In another word, the impact of debugging a connection to other connections on the server is similar to have a long-running connection.


 


The easiest way to develop and debug CLR code running in SQL Server is to use a C# or VB SQL Server project in Visual Studio.  When you deploy a SQL Server project, Visual Studio deploys the CLR assembly and its symbol file (.pdb) and source code files to the database so that the assembly is ready for execution and debugging.  


 


When you have a CLR assembly built in other ways, you can also use CREATE ASSEMBLY statement to deploy it to the database.  In this case you need to use ALTER ASSEMBLY ADD FILE to attach the symbol file (.pdb) and source code files to the assembly, and then you can use Visual Studio to debug the assembly.  If you forget to add a symbol file or source file, step-in or breakpoint in the corresponding CLR code will be skipped by Visual Studio.


 


In Visual Studio there are 3 ways that you can start debugging of T-SQL or CLR code in the database:


. Direct Database Debugging (DDD).  You can open Server Explorer and add a Data Connection to a SQL Server database.  Then you can browse to any T-SQL or CLR object in the database, such as a stored procedure or function, right click on it, and select Step Into to begin DDD.


. Debugging from a SQL Server project.  When you start debugging from a C# or VB SQL Server project (e.g. by pressing F5), the assembly will be built and deployed, and the default test script will be run in debugging mode.  Breakpoints in the default test script or in any T-SQL or CLR code called by the default test script will be hit.  You can also right click on any test script in the project and select Debug Script.


. Application debugging.  You can use Visual Studio to attach to any client application that opens a database connection to SQL Server, e.g. SQL Management Studio, and then you can debug T-SQL or CLR code executed on this connection.  When you attach to the client process, you need to make sure the debugging type includes T-SQL code, along with any other desired types such as native code and/or managed code.  There are some notable limitations in application debugging:


. You have to attach to the client process before the database connection is opened.  Any code run on the connections opened before attaching will be ignored.


. A Data Connection to the server being debugged must present in the Server Explorer.  Connections made by the attached client to databases not listed in Data Connections in Server Explorer will not be debugged.


. You can not step into a T-SQL or CLR store procedure from client code (managed or native).  Usually you need to set a breakpoint in the T-SQL or CLR code that you want to debug.  To do this browse to the desired object in Server Explorer, double click on the object to open its source code, and set a breakpoint.


 


The following are some imitations of T-SQL and CLR debugging in SQL Server 2005:


. T-SQL debugging is on the statement level.  You can not step into the execution of a select statement.


. Visual Studio 2005 is needed to debug T-SQL.  Microsoft SQL Server Management Studio doesnt support debugging.  Some third party tools that can be used to debug SQL Server 2005 may be released in the near future (or may have already been released).


. Break into CLR code in one connection freezes all connections that are running CLR code.  CLR debugging doesnt have connection isolation that is available in T-SQL debugging, because of limitations in CLR debugging architecture.


. Debugging doesnt work with parallel compiled plan execution.  When a batch or stored procedure being debugged is compiled to a parallel plan, debugging may not work.  In this case you need to disable parallel plan generation to debug it.


 


[ Cross posted from http://blogs.msdn.com/sqlprogrammability ]


Comments (15)

  1. webabcd says:

    SQL Server 2000可以让我们在查询分析器中直接调试存储过程(更多的信息请参看:用SQL Server 2000的查询分析器调试存储过程) 到了现在的SQL Server 2005,这个功能被转移到了SQL Servr Management Studio和Visual Studio IDE内。 使用这个技术,我们就可以在Visual Studio中一步一步地调试存储过程。 而且你也可以在存储过程中设置断点,这样当你调试你的程序时,这些断点就会生效。

  2. 勤勤同学 says:

    SQL Server 2000可以让我们在查询分析器中直接调试存储过程(更多的信息请参看:用SQL Server 2000的查询分析器调试存储过程) 到了现在的SQL Server 2005,这个功能被转移到了SQL Servr Management Studio和Visual Studio IDE内。 使用这个技术,我们可以在Visual Studio中一步一步地调试存储过程。 而且你也可以在存储过程中设置断点,这样当你调试你的程序时,这些断点就会生效。

  3. lqf111aaa says:

    在Visual Studio 2005中调试SQL Server 2005的存储过程

  4. assassintor says:

    在Visual Studio 2005中调试SQL Server 2005的存储过程

  5. huojia0908 says:

    在Visual Studio 2005中调试SQL Server 2005的存储过程

  6. pengyouba says:

    [翻译]在VisualStudio2005中调试SQLServer2005的存储过程 Postedon2007-07-0819:13webabcd阅读(7658)…