How do I set a breakpoint in a CLR Stored Procedure already deployed and running on a live SQL Server and debug is using Visual Studio?

For information on setup and debugging a CLR stored procedure in a direct-debug scenario by “stepping-through” the code from within Visual Studio, OR by using a test TSQL script from with Visual Studio, refer to the following MSDN article:

https://msdn.microsoft.com/en-us/library/ms165051.aspx

Now, suppose you have a stored procedure already deployed to a remote SQL Server that you need to debug by setting a break-point because you don’t know what condition is leading to the code path that is taken – you don’t know what input variables to provide to “step-through” the code from within Visual Studio using a direct debug by right clicking the stored procedure and selecting “Step Into”. Or perhaps, you want to set a conditional breakpoint to halt execution under a certain situation…

NOTICE: Do not do this on a production server – you will halt execution of all managed threads on the SQL Server and can lead to a crash of the SQL Server – only perform this on a development server!!!

In the following example, we have a CLR Stored Procedure in the AdventureWorksPTO database on a remote SQL Server – SQLTEST100 (a SQL Server 2008 box running on Windows 2008 R2). The AdventureWorksPTO database is an enhanced version of the AdventureWorks database PFEs use in our SQL Server 2005/2008 Performance Tuning and Optimization workshops. The stored procedure is in the assembly named AdventureWorksPTO_CLR_Debug_Example.

So we start out opening our project that contains the code for the CLR stored procedure:

Next, set a breakpoint in the code and setup our symbols by selecting Tools –> Options

Select the Debugging section and add the path to your PDB files for the project to the “Symbol file (.pdb) locations:” box.

On the remote computer (the SQL Server), you must have the Visual Studio Remote Debugger installed and running… If you need to install it, you can find it on the Visual Studio Installation Media. Here we start the Remote Debugger (MSVSMON):

You must provide the debugger a name string to listen for connections on… I change the name to DEFAULT@SQLTEST100:

Now it is listening:

Now, back on the development box with the Visual Studio project open, go to Tools –> Attach to Process:

Now we must specify our connection string name we specified in the Remote Debugger on the SQL Server, select the SQL Server process (sqlservr.exe) and then click to attach:

Once you have entered the connection string name, picked sqlservr.exe and clicked ATTACH, you will get a warning:

Now, using ANOTHER process separate from the Visual Studio Project, we make a call to the stored procedure. This simulates our production application calling into the stored procedure. In this example, we can just call it manually using SSMS on the SQL Server itself – SQLTEST100:

Go back to the development box into Visual Studio, and you will have hit the breakpoint you set inside the code:

From here you can do all the normal debugging you are used to with Visual Studio – set watches, change values, etc…

Here we have set a breakpoint on our stored procedure implemented using managed code (CLR code) that is already deployed to a remote SQL Server and used Visual Studio for debugging it.

- Jay

Comments

• Anonymous
  April 25, 2010
  Hi JayNice article. Thanks!This all works fine for me using SQL 2008 Developer Edition with VS 2008 Team Suite. I have just installed VS 2010 Ultimate :-) but am unable to debug after attaching to sqlserver.exe.According to the Output window, the attach is successful but it seems to hang the unser function somehow: After attaching to sqlserver.exe, I run the function in SSMS and the query runs indefinitely, instead of changing context to VS 2010. Once I detach from sqlserver.exe, SSMS returns immediately.Any help on this issue would be greatly appreciated.ThanksSeanp.s. VS and SQL Server are locally installed on my laptop. I am a member of the sysadmin fixed role and am a local admin on my laptop.
• Anonymous
  April 28, 2010
  Hi Sean,Sorry for the delay.  My first suspicion would be privileges - but I see you say you are a member of sysadmin and a local admin on your laptop.  Do you have this problem with a new project from VS 2010?  As long as you are broken into sqlservr.exe, the query running (really waiting) indefinitely is what you'd expect to see.  Then when you gracefully detach from sqlservr.exe, it should continue.   Let me know if you have tried this with a new project and experienced the same issue - or if this is specific to the project you are working on?Thanks,Jay
• Anonymous
  June 08, 2010
  My goodness, they could have made this easier.  After all, 15 years ago we could step through multiple processes for an out-of-proc COM interface, why can't we debug through a stored procedure (even locally) simply by stepping through the code that calls it?