SSDT SQL Server Debugging Doesn't Hit CLR Breakpoints

Question

I applied the SQL Server Data Tools patch to Visual Studio 2012 (Premium) and created a SQL Server CLR user-defined function project in C#:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 Add42(SqlInt32 in_param)
    {
        SqlInt32 retval = in_param + 42;  // Set break point here.
        return retval;
    }
}

In the SQL Server Object Explorer pane, I right-click on the newly published UDF and select "Execute Function..." I am prompted to supply a sample input value, and Visual Studio then publishes the function (again) to my local 2012 SQL Server and generates a script that looks like this:

DECLARE    @return_value Int

EXEC    @return_value = [dbo].[Add42] @in_param = 5

SELECT    @return_value as 'Return Value'

GO

... and executes it, returning the expected result of 47.

If I now put a break point on an executable line in my CLR UDF C# code, right-click the UDF function in SQL Server Object Explorer, and this time select "Debug Function...", I land in a debugger for the generated SQL test script. I can step through the SQL statements to the end of the script, which returns the correct result, but the breakpoint in my C# code is never reached in the C# debugger.

The terminology for this feature seems misleading. To any programmer, "debugging" a function means stepping through the executable lines in the code of the function itself. Simply generating a SQL test harness that calls my compiled function and gets back the result is just "testing" the function. At most, the only thing being "debugged" is the tool-generated test itself, because you can't "Step Into" the CLR code. The only option is to "Step Over" it.

So how do I get Visual Studio to actually debug, and hit the breakpoint in my UDF C# code?

Adularia

Have you tried stepping through the steps of this MSDN document to see if you skipped a step? – Warfarin

@Scott - I tried to, but after telling you how to create a SQL CLR function or sproc - which I've obviously already done - all that article gives you is Visual Studio debugging 101. You know: "right-click a line and select "'toggle breakpoint'", etc. Then, it says to "Add a script that tests the type.": "In Solution Explorer, expand the TestScripts directory and double-click Test.sql" I see no "TestScripts" directory in Solution Explorer in either 2012 or 2010, so I have no idea what they're talking about. – Adularia

Answer 1

Okay, I finally figured this out. To debug SQL CLR code in VS 2012:

1. Create a SQL test script that calls the UDF, sproc, or other CLR object. (You can do this by using the "Execute Function" or "Debug Function" options in the Server Object Explorer, as described in the question.)

2. Save the generated script. (It will be called something like "SQLQuery1.sql" by default. You may wish to give it a more meaningful name.)

3. In Solution Explorer, right-click the UDF (or other CLR type) project, and select "Properties".

4. The project's properties tab will open. On the left, select the "Debug" category.

5. In the "Start Action" subcategory of the Debug panel, select the "Startup script:" radio button. This will enable the associated dropdown so that you can specify the .sql script created in Step 1.

6. Save all, toggle a breakpoint on an executable line of your C# or other .NET language code, and press the debug button.

NOTE: You may now get a dialog telling you that "Windows Firewall has blocked some features of this program." I checked the boxes to allow access to domain and private networks.

Proceeding now should cause your breakpoint to be reached.

Adularia

PLEASE NOTE: It is also important that, in the SQL Server Object Explorer pane, you right-click on the server and select "Allow SQL/CLR Debugging"! – Adularia

ALSO NOTE: As mentioned below, you should run Visual Studio in Administrator mode. – Adularia

Followed everything. There is no Allow SQL/CLR Debugging option anywhere in Database project properties or the server explorer connections pane. – Horary

Ok Confused SQL Server Object Explorer with Server Explorer – Horary

Answer 2

For Visual Studio 2015 + Update 2:

In SQL Server Object Explorer pane, right-click on the server and select "Allow SQL/CLR Debugging":

In Server Explorer, right click on the function you want to debug, and select Execute:

It will generate the code for you. Select Execute with Debugger:

You can then place a breakpoint in your C# code, and it will hit it.

It will ask to open a port in your firewall, and it will ask to attach to SQL Server.

Insula

These are correct steps. But I still get the error when doing debugging.The tip is to start VS 2015 in elevated mode (i.e. run it as administrator) and this solves my issue (my current environment is windows 10 Pro + VS 2015 Update 3) – Sclerosed

Answer 3

I do not know if SSDT changes this, but in VS2008 I debug a .net UDF as follows:

• I deploy it to my local SQL server,
• then I attach VS to the SQL Server process (Menu Debug/Attach to process/sqlserver.exe, if SQL Server is running as a service it requires that VS was started as administrator).
• Then execute some SQL code calling the UDF, e. g. in Management Studio. Maybe this will work from SSDT in VS 2012.

Susumu

Frank, explicitly attaching is not necessary in VS 2012. See my answer above. – Adularia

Answer 4

The patch you applied may install VS items which are not current with the Visual Studio Quarterly update. I recommend that you now apply the latest Visual Studio Quarterly Update for VS 2012.

Wesson

This was a good suggestion, and I followed it and applied the quarterly update on top of my SSDT patch. However, it did not change the debug behavior or the Solution Explorer directory structure. – Adularia

@Adularia I was working on Windows phone app and installed a Azure addon. Then my WP8 emulator would not come up because Azure installed components in VS which were previous of my current patch. Glad you got it resolved. – Anthracosis