Debugging CLR Database Objects

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.

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 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 "SQL Server CLR Integration Database Debugging" topic in the Visual Studio documentation.

The TCP/IP network protocol must be enabled on the SQL Server instance in order to use Visual Studio 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

Example

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);
      }
   }
}

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

EXEC GetVersion

See Also

Other Resources