Debugging CLR Stored Procedures in Visual Studio 2010

One of the problems I encountered recently was a CLR stored procedure that I had created which accepted accepted a file and wrote this file locally, while putting a row in a database to reference its location.

The problem came around when I tried it out and it through an UnauthroizedAccessException. So I though straight away, fine I guess I haven't given the directory permissions for the SQL Service user to write. So I added this and tried again but I got the same error! Intrestingly if I had been sensible and looked up the MSDN documentation for File.WriteAllBytes(), which I was using to write the transfered file to disk, I would have seen that UnauthroizedAccessException is actually thrown for more reasons than just security permissions, in fact it says I can occur for:

 path specified a file that is read-only.

-or-

This operation is not supported on the current platform.

-or-

path specified a directory.

-or-

The caller does not have the required permission.

So if I had heeded this information,. I would have found that my bug was caused by quite a noob mistake where I was trying to write to a directory and not a file.

Either way though I didn't, and I wanted to understand how to debug CLR procedures incase a real dire problem came up in a more complex procedure. So this is how I achieved it:

1) Re-create your CLR stored procedures in SQL Server using a debug version of your assembly. Make sure you have the symbol file for it. You'll need it to be able to catch break points and debug the assembly. In my set-up both assembly and debug symbol file were both in the same folder.

2) Add this folder to your symbol directory list in visual studio. It may be that visual studio will be able to find the symbols anyway, but I guess it doesn't harm to give VS a bit of a hint as to where to look. This can be done by going to Tools > Options > Debugging > Symbols and adding the new location in the 'Symbol file (.pdb) locations' box.

Adding a Path to the Assembly Symbol Files.

3) Ensure the setting for debugging 'Just My Code' is turned off. This can be verified by going to Tools > Options > Debugging > General and making sure that the 'Enable Just My Code' check box is unchecked.

Changing Debug Settings

4) Ensure that the assembly that contains your stored procedures is the active solution in Visual Studio. At this stage we can attach Visual Studio to the SQLServer process which is using our CLR stored procedure assembly. To do this, go to Debug > Attach To Process. This will open up a window showing all the processes we can attach to. Check both 'Show processes from all users' and 'Show processes in all sessions'. In this list above, you should be able to find a process called something like sqlservr.exe. If the process has loaded your assembly you wish to debug, the type should include 'Managed'.

Attaching to the SQL Process

On attaching to this process, checking the output should show that its found and loaded the symbols successfully for your assembly containing the stored procedures. If everything is ok, the breakpoints in the stored procedure assembly should become active too (go from outlines to filled)! Click 'Attach'.

To check if the correct assembly has been loaded after attaching the the right symbols, you can view the loaded modules in visual studio. To do this click Debug > Windows > Modules.

Modules View

If your symbols haven't been loaded, you can always try and load them manually by right clicking and selecting load symbols. If this still doesn't work, ensure that your symbol path has a root to your stored procedure assembly symbol file.

If it isn't exactly the same .pdb for the source you have open in visual studio, the break point may not still be filed and it won't catch, but the differences may be small enough for you to understand the problem, so you can resolve this by right clicking on the break point, selecting 'Location' and checking 'Allow the source to be different...'.

5) Run the application past the point where it uses the stored procedure and the break point should catch! And your free too debug!

This indeed was how I found my noob mistake! Good experience though! :)

There was one problem I encountered upon the way though, and that was that I kept getting the error message:

'Attaching T-SQL debugger failed. The debugger is not properly installed.'

Interestingly enough for me it was a problem with having database connections in the Server Explorer tab of Visual Studio. Removing these caused the error to go away and my debugging to work!

Also, using release versions of the stored procedure libraries didn't seem to work for me, although I may be overlooking something here.

Anyway, I hope this helps someone along their way! :)