Wednesday, August 4, 2004 2:58 PM nielsb

SQL Server 2005 Debugging


There has been quite a few discussion on some of the beta newsgroups about how to debug CLR methods, running in SQL Server 2005 (S2k5) as stored procedures, functions, etc. (actually there are some questions about how to debug T-SQL code as well). I thought therefore that it'd be a good blog post, discussing debugging.

[DISCLAIMER - This is local debugging only, and it works on my machine - YMMV]

So, to begin with; IMO there are three different ways of doing debugging against SQL Server 2005 (here I do not take into account debugging the "guts" of SQL Server using Windbag etc):

  • SQL Server Project Debugging
  • Class Project Debugging
  • Server Explorer Debugging

let's have a look at all three of them.

SQL Server Project Debugging - This is probably how you will debug (at least initially) if you create your .NET procedures, functions etc. by using the SQL Server Project in Visual Studio 2005 (VS2K5). This project type allows you to do F5 debugging. In other words:

  • you choose the database connection you want to use,
  • you create your .NET code, adorn the methods with the deployment attributes (SqlFunction, SqlProcedure, SqlTrigger, etc),
  • you set your breakpoints (BP's) and you hit F5.
  • the project will be compiled, the assembly deployed to SQL Server, the stored procs, functions etc, created  and your method will be executed in SQL Server.
  • theoretically your BP should now be hit. 

However I bet that the first time you do this it won't happen. This is because you need to indicate that you want to do SQL/CLR debugging. You do this in the Server Explorer (NOT Solution Explorer):

  • right click on your database connection
  • choose "Allow SQL/CLR Debugging"

Now when you hit F5, the BP will be hit and you can step through the code. If your CLR code calls into "normal" T-SQL code, you'll step from CLR to T-SQL and back, Sweet!! In the Beta1 build there is an "issue" however; if your CLR code executes a reader with dynamic SQL, something like so:

SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "Select id, name from emp";
SqlDataReader dr = cmd.ExecuteReader();
//do something else down here

In this scenario when you execute the reader, your debugging session will be taken to a Dynamic Sql page, and you'll not be able to exit from there! You'll not hang, the code will execute OK, but you won't see anything beyond the Dynamic Sql. As I said, this is a Beta 1:ism (sometimes I've been able to pass the Dynamic Sql, but most of the times not). If you execute a NonQuery or call into a T-SQL procedure, it'll work OK.

The final thing worth noticing with this type of debugging is that it works due to a sql script-file (Test.sql), which is used to execute your proc/function. This is not dynamic, so if you change the method name, add parameters etc, you need to change the file as well.

Class Project Debugging (a.k.a Attach to Process) - What if you do not use the SQL Server Project type, or you do not use VS2K5 as your IDE, how do you debug then? Well, you use the "old fashioned" way of attaching  a debugger to the server process (if you don't have the VS2K5 IDE, the .NET Framework ships a CLR GUI Debugger):

  • you create your .NET code and compile
  • you deploy the assembly to the database
  • you catalogue the CLR methods as stored procedures, functions etc.
  • you attach to the SQL Server process and you set the BP's in your code  (note that initially the debugger tells you that the BP won't be hit as no symbols have been loaded - you can ignore that).
  • execute the CLR proc from SQL Server Management Studio (SSMS) and the bp will be hit and now you can step through the code.

In this scenario, you will not have the same issue as above with a DataReader. This is because the debugger has no real knowledge of the T-SQL code, so you will never step into the Dynamic Sql. In addition you will not be able to step from CLR code to T-SQL code either.

By now you probably say that this is all good and well, but what about debugging T-SQL code. In SQL 2000, in the Query Analyzer, I could right click on a stored proc and choose Debug and step into the proc.  How do I do it in S2K5? Well, at the moment, the SQL Server Management Studio does not have the capabilities of debugging, so that's where the third way of debugging comes into play: Server Explorer Debugging.

Server Explorer Debugging - In this type of debugging you use, as the name implies, the Server Explorer pane from within the VS2K5 IDE. If you want to debug a pure T-SQL procedure/function you:

  • Go into the Server Explorer and choose the database connection you are interested in.
  • Expand the connection and drill down into the Stored Procedures/Functions folder.
  • Choose the procedure/function you are interested in, right click and choose "Step Into Stored Procedure" ("Step Into Function" if it is a UDF).
  • this takes you now into the proc/function and you can step the code!!

If you want to debug from T-SQL into CLR you need first to set the "Allow SQL/CLR Debugging" option on the connection as I mentioned above. You can also debug pure CLR code this way. Load the project and choose the CLR procedure/function from the Server Explorer and do "Step Into ...", Cool!!!

OK - so that's just a little bit about debugging. As mentioned in the disclaimer: this is local debugging only, it works on my machine BUT: YMMV.

If you have comments etc, please leave them here or email me!


Filed under:

Comments

# re: SQL Server 2005 Debugging

Thursday, January 24, 2008 4:04 PM by John D

Probably a stupid question, but when you are doing simple Server Explorer debugging - that is, just debugging a stored procedure w/o CLR code - how do you see the values of temporary tables - or any table for that matter?   Tooltips and Watches show the value of scalar variables (@varx, etx),, but where do you enter (while you are at a breakpoint)  "SELECT * FROM #TempTable" or something similar?   I've tried the immediate window, but it always says {expression}  could not be evaluated.

# Nestor Sulikowski » Debugging SQLCLR

Thursday, May 28, 2009 9:58 PM by Nestor Sulikowski » Debugging SQLCLR

Pingback from  Nestor Sulikowski » Debugging SQLCLR