Debugging CLR Database Objects

You must also use Visual Studio 2005 if you want to use the debugger against CLR database objects. You can use the standard set of debugger features:

• Use Step In, Step Over, Step Out, and Run To Cursor to control execution of code.

• Step in from the CLR database object code into Transact-SQL code and vice versa.

• Investigate or modify values of variables.

• Investigate results and messages that are returned to the caller.

• Use breakpoints to pause execution of the CLR code.

SQL Server projects (in Visual Studio 2005) contain a Test Scripts node (see Solution Explorer). Scripts in this node are entry points into the debugger. By default, the node contains just the Test.sql script. Everything in this script is initially commented out. You can enable a segment or simply add new lines to execute the target database objects. The batch in this script should work similarly to batches executed from the Query window in Management Studio.

Note 

Alternatively, you can create a new script under the Test Scripts node. In this case, you can select which test script you want to use by choosing Set As Default Debug Script in the context-sensitive menu for the script.

To start debugging you should follow these steps:

1. Set the breakpoint in the CLR object (or Transact-SQL code) where you want to start debugging.

2. Choose Debug | Start Debugging (or press FS).

Let's walk through debugging a simple managed stored procedure:

1. Create a new database project, for example, in C#.

2. Add a stored procedure (template) to it.

3. Change the code of the stored procedure template to be something like this:

         using System;      using System.Data;      using System.Data.SqlClient;      using System.Data.SqlTypes;      using Microsoft.SqlServer.Server;      public partial class StoredProcedures      {          [Microsoft.SqlServer.Server.SqlProcedure]          public static void cp_DebugTest()          {              int count = 1;              SqlContext.Pipe.Send("Hello world 1!\n");              count++;              SqlContext.Pipe.Send("Hello world 2!\n");              using (SqlConnection conn =                  new SqlConnection("Context Connection=true'))              {                  string EqType = "";                  // Set up the command object used to execute the stored proc                  SqlCommand cmd = new SqlCommand("dbo.ap_EqType_List", conn);                  cmd.CommandType = CommandType.StoredProcedure;                  //execute sp                  conn. Open () ;                  using (SqlDataReader reader = cmd.ExecuteReader())                  {                       while (reader.Read()) // Advance one row, until you can                       {                          // Return output parameters from returned data stream                         //id = reader.Getlnt32(0); // do not need first column                         EqType = EqType + reader.GetString(1) + ", ";                       }                  }             }          }      }; 

4. Position the cursor on the first line in the method and press re to set the breakpoint.

5. Open Test.sql from the Test Scripts node of Solution Explorer and add the following code to it:

         Exec dbo.cp_DebugTest 

6. Choose Debug | Start Debugging from the menu (or press FS). The program will spend some time hooking the debugger to the connection that is being executed. You can see its progress in the Output pane of Visual Studio.

7. Finally, the debugger will stop on the breakpoint (see Figure 16-2). The yellow error will point to the line that needs to be executed next.

   
   Figure 16-2: SQLCLR debugging

8. This is the point where you can take control of the execution (using comments as in Debug | Step Into) or investigate and set the values of variables. Start pressing FII (Step Into) and the debugger will eventually step into the Transact-SQL stored procedure (see Figure 16-3).

   
   Figure 16-3: Stepping into a procedure written in a different language

9. Continue executing Step Into and the program will step out of the Transact-SQL procedure and go back to the CLR stored procedure.

During all of these steps, all result sets and string messages (such as strings in the Print statement or Raiserror) will be collected in the Output window, and you can scroll up and down to review them.

The Visual Studio debugger can only hook to connections initiated by these scripts, not to existing connections initiated from some other client such as Management Studio. It is possible to debug multiple Transact-SQL sessions on one server at one time, but this practice could lead to locking issues on the server. On the other hand, it is possible to debug only a single session at a time for any CLR database object.

Note 

Only members of the sysadmin server role are allowed to debug code on SQL Server.