Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Debugging SQL Server CLR functions, triggers and stored procedures


By:   |   Last Updated: 2008-11-11   |   Comments (1)   |   Related Tips: More > Debugging

Problem
One of the nice things about developing your SQL Server CLR code in Visual Studio is that you get to take advantage of the debugging aspects of the tool.  When developing it is always beneficial to use a debugger and step through your code to find any coding problems, but how is this done when writing CLR code for SQL Server?

Solution
In order to take advantage of the debugging aspects for your SQL Server CLR code you need to be using either the Professional or Team Edition of Visual Studio.  The following table outlines the edition and which languages you can use the debugger.

Visual Studio Edition Visual Basic C# C++ J# Visual Web Developer
Express No No No No No
Standard No No No No No
Pro/Team Yes Yes Yes No No

(source: SQL Server Books Online)

In addition to  having the correct version of Visual Studio and one of the supported languages you also need to make sure the CLR is enabled.  Here is a tip that refers to this http://www.mssqltips.com/sqlservertip/1344/clr-string-sort-function-in-sql-server/.

It is important that you know that when you are debugging this will cause all other threads to stop as well, so this is why it is key that you do not try to debug on a production server.

Once you are set you need to enable the debugging for the connection using the "Server Explorer" in Visual Studio as shown below.  Server Explorer can be found under the "View" menu item in Visual Studio.

 When you enable debugging you will get this message as mentioned above.

Once you have the CLR enabled for SQL Server and the connection has been enabled for debugging you can begin the debug process. 

There are three ways you can debug CLR code:

  1. Stepping into a SQL Server 2005 database object using Server Explorer
  2. Running a test script using a Visual Studio 2005 SQL Server project
  3. Running an application that calls a stored procedure

Process 1 - Stepping into an object

If you have already deployed your CLR code to your SQL Server you can browse for the CLR function or SP, right click on it and select "Step Into Function" or "Step Into Stored Procedure" as shown below for the function we are debugging.  This is the same function listed in this previous tip http://www.mssqltips.com/sqlservertip/1344/clr-string-sort-function-in-sql-server/.

This will execute the function or stored procedure and will pop up a window so you can enter in values for the parameters as shown below.

After you enter the value and click "OK" the debugging process will begin.  The first place it will stop is at the beginning of the function code whether you use a breakpoint or not as shown below.

At this point we can step through each line of the code using the debug controls toolbar as shown below or by using the options in the "Debug" menu.

In the Locals window you can see the variable values as they change as shown below. In addition, you can take advantage of other debugging features as well.


Process 2 - Using a test script

Another way is to create a test script and then debug the test script.  The test scripts get created in your solution in the "Test Scripts" folder.

Here is what the code in our Test1.sql script looks like. This is going to run the sortString function with the values shown below.

After you have set your breakpoints, to start the debug process right click on the test script and select "Debug Script".  One thing to note for this approach is that you have to set breakpoints otherwise the debugger will not stop it will just execute all of the code.  This is one difference from Process 1.

Once the debug process starts it will stop at the first breakpoint as shown below.

At this point you can step through each line of code and see the values as they change.


Process 3 - Test with an application

In this process you are debugging your application. If the application calls the SQL Server CLR code and you have breakpoints setup you can debug the code.


Next Steps

  • Although this is not a comprehensive end to end process for coding and debugging it should hopefully give you enough information to begin debugging your code
  • To debug a trigger you can create a test script that does an insert, update or delete against your table and it will allow you to debug your trigger code as long as you have setup breakpoints.  Another way this could be done is to debug a stored procedure which in turn fires the CLR trigger.


Last Updated: 2008-11-11


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 03, 2010 - 9:47:33 AM - sgperlman Back To Top

Hello,

For some reason when I attempt to debug any of my CLR UDFs (running Visual Studio 2008 Pro / MsSQL2005) my system always seems to lock up.  My functions works well (when not attempting to debug) ...

My output window is shown below up to the part where everything locks up.  No matter how long I wait for something to happen (such as the code to break at the start of the funciton and wait for me this is where it will die.  All I can do is stop debugging; the funciton then returns the expected result and warns me about the debugger now being unstable ...  any thoughts on what may be wrong would be appreciated.

 Thanks.

 

The thread 'sqldev [62]' (0x1984) has exited with code 0 (0x0).

The thread 'sqldev [64]' (0x1984) has exited with code 0 (0x0).

The thread 'sqldev [64]' (0x1984) has exited with code 0 (0x0).

The thread 'sqldev [64]' (0x1984) has exited with code 0 (0x0).

The thread 'sqldev [64]' (0x1984) has exited with code 0 (0x0).

Auto-attach to process '[256] [SQL] sqldev' on machine 'sqldev' succeeded.

Running [dbo].[Qantel_Cost_and_Sell] ( @sCustNo = 1058, @sSubAcct = 0, @sPartNo = 04316810004, @sQantelRegion = 01 ).

The thread 'sqldev [64]' (0x1984) has exited with code 0 (0x0).

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll'

'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll'

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll'

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll'

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll'

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll'

Auto-attach to process '[256] sqlservr.exe' on machine 'sqldev' succeeded.

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll'

'sqlservr.exe' (Managed): Loaded 'Qantel_System_Cost_and_Sell_API', Symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll'

'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'


Learn more about SQL Server tools