debugger doesn't stop at breakpoint while debugging SQLCLR function

Amit Jitendra wrote:

Hi, I have created one simple SQL Server 2005 CLR function in Visual Studio 2005 beta 2. It works fine from SQL Server 2005 management studio April CTP. When I try to debug this SQL CLR function from Visual Studio 2005, I am getting the output in Output window along with debug information but it is not stopping at breakpoint. Output window says ‘No symbols loaded’. Could you please help me why it is not stopping at break point? I checked configuration property and it is debug for project and solution. There is no Web.Conifg file, as it is Database project. So I changed the debug = true in machine.config file at all required places and restarted the PC.

You should not have to enter any debug information i any config files

Amit Jitendra wrote:

I selected the function in Server Explorer -> Database connection -> Assembly -> Function1. Note that for this connection, Application Debugging and Allow SQL/CLR debugging options are already selected. I right clicked the function and selected ‘step into function’. Then it is displaying following message box. “There is no source code available for the current location, and Ok and Show Disassembly buttons are displayed.”

Is the project loaded in VS when you do the above? I.e. the project needs to be available in order to do this. You can also try and delete the connection in Server Explorer, re-create the connection and at the "Allow SQL/CLR debugging" option.

Amit Jitendra wrote:

If I right click the function in server explorer and click Execute, it is displaying the result in output window, but if I right click function and click Open it displays “ No output available for Object” message box. In Debug mode, I went to Debug Menu -> Widows -> Modules and opened the Modules window. I located my module in Modules windows. Then I right clicked and clicked Load Modules. I browsed the bin/debug folder of my project and selected the .pdb file then it gave the message “The symbol file SqlServerProject1.pdb does not match the module.”.

Your module file you choose above has nothing to do with this. All modules are loaded from the database.

Amit Jitendra wrote:

I went to Debug menu and clicked Attach Process and selected Sqlwb.exe process and then tried to execute function from SQL Server management studio but no success. In this debug mode, F10 and F11 are not working in visual studio, when Sqlwb.exe process is attached to debugger. When I hovered the mouse over the breakpoint in visual studio, it displays “The Breakpoint will not currently be hit. The specified module has not been loaded” tooltip. Kindly let me know if I am missing something. Thanks, Amit.

The process you should attach to if you want to do this type of debugging is the sql server process; sqlservr.exe (I believe that's the name - I don't have a machine handy to check).
I suggest you rebuild your assembly, do a new deployment and tries again. Make sure your project is up and running when you try to do the debugging. If you do a "Step into ...." from server exploere you at least need the source file open in VS, and the source has to be the same as what's in the database.
I wrote a year ago a blog-entry about debugging, you can find it here. Be aware that it is fairly old though. I am writing an article at the moment for SQL Magazine about debugging, but that won't be published for a while yet.

Thank you very much.

I am able to debug the SQLCLR function step by step using F10 and F11.

It seems remote debugging component was not installed during Visual Studio Installation. I installed the remote debugging component in VS.

When I expand the Database connection in Server Explorer, there is assembly for my Visual Studio project in Assemblies folder.

If I expand that assembly my function name is listed there. If I right click function name and click Open it is opening the source code for function in Visual Studio.

I click Step into Function and I am able to debug the function step by step.

Same Visual Studio project has stored procedure as well but it is not listed under above assembly.

Stored Procedure is in separate assembly which is created in SQL Server Management Studio. If I right click the Procedure in that assembly and click Open, I am getting message “No Source code is available for this object” message. If I click Step into Stored Procedure, it is not stopping at break point set in Stored Procedure.

Here is my Store Procedure

sing System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class FirstCLRProc

{

[SqlProcedure]

public static void HelloWorld()

{

SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString() + "\n");

using (SqlConnection conn = new SqlConnection("context connection=true"))

{

conn.Open();

SqlCommand cmd = new SqlCommand("SELECT AverageLeadTime FROM Purchasing.ProductVendor", conn);

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

}

}

}

Could anybody please tell me why debugger is not stopping at breakpoint in above Stored Procedure? Note that break point is working fine for me for SQLCLR function.

Thanks,
Amit.

Thanks Niels.

I found the solution for the problem.

I created the SQLCLR stored procedure in Visual Studio 2005 Beta2.

Then I created the assembly in SQL Server 2005 by following commands in SQL Server Management Studio.

CREATE ASSEMBLY StoredProcedure1 FROM 'C:\StoredProcedure1.dll'

CREATE PROCEDURE HelloWorld

AS EXTERNAL NAME StoredProcedure1.FirstCLRProc.HelloWorld

Now when I go to Server Explorer in Visual Studio and expand the database connections, I get the Assemblies folder.

I found assembly with the name StoredProcedure1 and method HelloWorld inside that assembly.

When I right click the stored procedure and select ‘Step Into Stored Procedure’, it does not stop at the breakpoint and directly displays results in output window.

Solution:

I right clicked the project in Visual Studio and clicked Deploy.

Then I refreshed the Assemblies folder in Server Explorer, I got assembly with the Project name. Inside this assembly, I could locate my Store Procedure.

Now if I select the Stored Procedure method and click Open, it is opening the source code in Visual Studio. If I right click and select Step Into Store Procedure, debugger stops at first statement in that method and then I am able to debug the stored procedure one step at a time using F10 and F11 keys.

Thanks,

Amit.