Share via

Debugging CLR Database Objects

  • Article

Microsoft SQL Server provides support for debugging Transact-SQL and common language runtime (CLR) objects in the database. The key aspect of debugging in SQL Server 2005 is the ease of setup and use. Furthermore, debugging works across languages. Users can step seamlessly into CLR objects from Transact-SQL, and vice versa. SQL Server 2005 does not include a debugger, but Microsoft Visual Studio 2005 Professional Edition and Visual Studio 2005 Team Suite Edition both support debugging of managed database objects in SQL Server 2005. Managed database object debugging supports all common debugging features, such as "step into" and "step over" statements within routines executing on the server. Debuggers may set breakpoints, inspect the call stack, inspect variables, and modify variable values while debugging. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming or debugging. SQL Server 2005 includes the .NET Framework 2.0 pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

For more information about debugging managed code using Visual Studio, see the "Debugging Managed Code" topic in the Visual Studio documentation.

Debugging Permissions and Restrictions

Debugging is a highly privileged operation, and therefore only members of the sysadmin fixed server role are allowed to do so in SQL Server 2005.

The following restrictions apply while debugging:

  • Debugging CLR routines is restricted to one debugger instance at a time. This limitation applies because all CLR code execution freezes when a break point is hit and execution does not continue until the debugger advances from the break point. However, you can continue debugging Transact-SQL in other connections. Although Transact-SQL debugging does not freeze other executions on the server, it could cause other connections to wait by holding a lock.
  • Existing connections cannot be debugged, only new connections, as SQL Server requires information about the client and debugger environment before the connection can be made.

Due to the above restrictions, we recommend that Transact-SQL and CLR code be debugged on a test server, and not on a production server.

Overview of Debugging Managed Database Objects

Debugging in SQL Server follows a per-connection model. A debugger can detect and debug activities only to the client connection to which it is attached. Because the functionality of the debugger is not limited by the type of connection, both tabular data stream (TDS) and HTTP connections can be debugged. However, SQL Server 2005 does not allow debugging existing connections. Debugging supports all common debugging features within routines executing on the server. The interaction between a debugger and SQL Server happens through distributed Component Object Model (COM).

For more information and scenarios about debugging managed stored procedures, functions, triggers, user-defined types, and aggregates, see the "Debugging SQL CLR Database Debugging" topic in the Visual Studio documentation.

Note

The TCP/IP network protocol must be enabled on the SQL Server 2005 instance in order to use Microsoft Visual Studio 2005 for remote development, debugging, and development. For more information about enabling TCP/IP protocol on the server, see Configuring Client Network Protocols.

To debug a managed database object

  1. Open Microsoft Visual Studio 2005, create a new SQL Server project, and establish a connection to a database on an instance of SQL Server 2005.

  2. Create a new type. In Solution Explorer, right-click the project, select Add and New Item… From the Add New Item window, select Stored Procedure, User-Defined Function, User-Defined Type, Trigger, Aggregate, or Class. Specify a name for the source file of the new type and click Add.

  3. Add code for the new type to the text editor. For sample code for an example stored procedure, see the section later in this topic.

  4. Add a script that tests the type. In Solution Explorer, expand the TestScripts directory double-click Test.sql to open the default test script source file. Add the test script, one that invokes the code to be debugged, to the text editor. See below for a sample script.

  5. Place one or more breakpoints in the source code. Right-click on a line of code in the text editor, within the function or routine you want to debug, and select Breakpoint and Insert Breakpoint. The breakpoint is added, highlighting the line of code in red.

  6. In the Debug menu, select Start Debugging to compile, deploy, and test the project. The test script in Test.sql will be run and the managed database object will be invoked.

  7. When the yellow arrow designating the instruction pointer appears at the breakpoint code execution pauses and you can begin debugging your managed database object. You can Step Over from the Debug menu to advance the instruction pointer to the next line of code. The Locals window is used to observe the state of the objects currently highlighted by the instruction pointer. Variables can be added to the Watch window. The state of watched variables can be observed throughout the entire debugging session, not only when the variable is in the line of code currently highlighted by instruction pointer. Select Continue from the Debug menu to advance the instruction pointer to the next breakpoint or to complete execution of the routine if there are no more breakpoints.

Example

The following example returns the SQL Server version to the caller.

C#

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class StoredProcedures 
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void GetVersion()
   {
   using(SqlConnection connection = new SqlConnection("context connection=true")) 
   {
      connection.Open();
      SqlCommand command = new SqlCommand("select @@version",
                                           connection);
      SqlContext.Pipe.ExecuteAndSend(command);
      }
   }
}

Visual Basic

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Partial Public Class StoredProcedures 
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared Sub GetVersion()
        Using connection As New SqlConnection("context connection=true")
            connection.Open()
            Dim command As New SqlCommand("SELECT @@VERSION", connection)
            SqlContext.Pipe.ExecuteAndSend(command)
        End Using
    End Sub
End Class

The following is a test script that invokes the GetVersion stored procedure defined above.

EXEC GetVersion

See Also

Other Resources

Database Engine .NET Framework Programming

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Updated content:
  • Added note about enabling TCP/IP connections on the server for remote deployment.
Updated content:
  • Added note about Visual Studio .NET 2003 and .NET Framework 2.0.