Debug Stored Procedure in .NET managed code

Did you know that it is possible to debug a stored procedure by tracing down the execution steps from managed code? If your answer is no, here is what you need.

1. Open a Windows application or ASP.NET project where you are invoking a stored procedure.
2. Set a breakpoint in the step preceding the SQL call.
3. Open Server explorer, drill down to the stored procedure that you want to debug, open it and set a breakpoint in the first executable statement.
4. Go to Project’s property pages (by right-clicking on the project name in Solution Explorer), navigate to Start Options category and select SQL Server debugger.
5. Run the project in debug mode.
6. When the breakpoint in managed code is reached, step through (F11) the subsequent steps. The IDE would automatically lead you to the stored procedure breakpoint.

If you find any difficulty in doing the above steps, please stop by my desk for a short demo. You can also refer to the Microsoft Support article mentioned below.

http://support.microsoft.com/kb/316549

Note: The login that you are using needs to be in sysadmin role in SQL Server.