sql server - How to debug stored procedure in VS 2015?

Question

I know this was asked many times, I've read them. I've checked the [Enable SQL Server debugging] in all my projects in the solution.

My projects:

• ASP.NET web app
• DAL class library. (The DAL uses a legacy .dbml to generate SP calling wrapper code.) No OR mapper neither direct ADO.NET used in the project.
• SQL Server 11.0.3153

I would like to debug my called SPs when debugging the C# code in VS. Ideally it should step in to the SP, but if this is not supported, then break in the SP on a set breakpoint.

Unfortunatelly it does neither. If I set a breakpoint in the SP, it is not a filled red circle, instead an unfilled, which is not a good sign. (I am setting this breakpoint in Server Explorer, by opening a data connection, and opening the SP in the VS editor.

What am I missing?

Answer 1

General instructions, based on my experience and research.

1. Run Visual Studio (community version, in my case) as Administrator (for me, debugging a stored proc from VS only works when VS is run as an admin)
2. Go to the Solution Explorer, right click on your project and go to properties.
3. Click on the Web tab and make sure that SQL Server is checked. Save and close.
4. Click on the View menu, then on SQL Server Object Explorer.
5. In the SQL Server Object Explorer, expand SQL Server and if you don't see your SQL Server, right click on SQL Server and add it.
6. Right click on the SQL Server that you just added and make sure that both Application Debugging and Allow SQL/CLR Debugging are checked.
7. Expand your SQL Server instance that you added and find the stored procedure of interest.
8. Right click that SP and click View Code.
9. Put a break point where you wish.
10. Run and enjoy.

You may have to do some of these things next time you wish to debug a stored proc from VS after you close VS and open it up later.