Debugging a CLR Assembly
Microsoft SQL Server provides support for debugging Transact-SQL and common language runtime (CLR) objects in the database. Debugging works across languages: users can step seamlessly into CLR objects from Transact-SQL, and vice versa.
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, only new connections. Debugging supports all common debugging features, such as step into, over, and out of statements within routines executing on the server. Debuggers may set breakpoints, inspect the call stack, inspect variables, and modify variable values while debugging. The interaction between a debugger and SQL Server happens through Distributed Component Object Model (DCOM).
SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio 2005 supports debugging connections to SQL Server. For more information about how to debug SQL Server 2005 CLR stored procedures, functions, triggers, user defined types, and aggregates from Visual Studio, see the Visual Studio 2005 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 breakpoint is hit, and execution does not continue until the debugger advances from the breakpoint. 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 can. This is because SQL Server requires information about the client and debugger environment before the connection can be made..
Due to the above restrictions, we recommend that CLR code be debugged on a test server and not on a production server.