Section 4.10. Testing and Debugging CLR Routines

SQL Server 2005 lets you debug both T-SQL database objects and CLR routines. SQL Server 2005 does not ship with a debugger, so you must use Visual Studio 2005.

Follow these steps to use the Visual Studio 2005 debugger to step through source code for registered CLR assemblies when executing T-SQL statements using .NET Framework routines:

Add the PDB filea file containing debugging and project state informationto the registered assembly. For example, to add the PDB file to the HelloWorld stored procedure project in the "Hello World Example" section earlier in this chapter, execute the following T-SQL statement:
```
    ALTER ASSEMBLY HelloWorld
    ADD FILE FROM 'C:\PSS2005\HelloWorld\HelloWorld\bin\Debug\HelloWorld.pdb'
```
In the Visual Studio 2005 IDE, select Debug Attach to Process.
Check the "Show processes from all users" checkbox in the Attach to Process dialog box.
Select sqlservr.exe from the Available Processes list box. Click the Attach button and close the dialog box.
Set a breakpoint in the source code at the following line:
```
    SqlContext.Pipe.Send("Hello world.\n");
```
Execute the stored procedure from SQL Server Management Studio:
```
    exec HelloWorldSP
```
Execution will stop at the breakpoint you set in the Visual Studio IDE.