Debugging SQL CLR Code

Over the past few months I have ran into problems with trying to debug through SQL CLR Code. Bottom line is that I couldn’t, but after the many annoying messages sent to David Hodge and the past 2 hours working through it with Joseph Styons it works! I will make another blog post next week about why I really needed this to work and why I had to use SQL CLR. Here it is. Screenshots courtesy of Joseph Styons and his many keyboard shortcuts.

In my solution I have 2 projects, one is a Blackbaud Catalog Project and the other is just a good old meat and potatoes VB Class Project. The catalog project has a Data List that calls a SQL Function and that SQL Function is what is calling our vb.

One important thing to note is that you have to change the target framework of the vb class to 3.5.

projectproperties

Project -> Properties -> Compile -> Advanced.
Once the properties have been changed let’s go ahead and add a class file to our vb project. And then there are some important things to note in the code that have to be added to any of the functions that will be called by our SQL UFN. Note the <Microsoft.SqlServer.Server.SqlFunction()> attribute and the SQL types being used for the incoming parameters and the return value.

projectcode

Once your CLR function has been created, build, deploy, and create your SQL assembly with the script below, obviously yours will be different.

createassembly

Now once that succeeds create your SQL UFN as a spec in the catalog project, but just for basic testing abilities we are doing it like this:

sqlfunction

Now add your Data List to BBEC that calls the above function. We used a generic one that had just one column called combo name and just did a select top 10 from the constituent table and passed in the first and last name to the above UFN. Once you have that done and everything loaded into the system then open BBEC and make sure that the Data List is working correctly.

Bring on the fun, debugging your CLR, or pain however you look at it.

First off we need to change the processor affinity on the SQL Server. Follow the screenshots below.

serverproperties

Right click on the server and go to properties.

jrcore

chriscore

Go to processors and then depending on how many cores you have, there will either 2 or 4. (Joseph Styons had 2 and ended up using the second core, whereas I have 4 and ended up using the first one. To each his own.)

After you set your processor affinity restart SQL Server and get ready to debug.

You will want to attach your process to sqlservr.exe from within Visual Studio 2010, but set the code type to T-SQL and Managed (2.0).

attach

Now you may get an error once you hit attach if you do and it looks like what is below just hit okay and keep going with it. Everything successfully worked after this error, so we just ignored it.

attacherror

Once you have everything set up the debug point should resolve, then refresh your Data List in BBEC and watch the

magic

Note: Change back your processor affinity settings back to “automatic” when you are finished debugging (for performance reasons).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s