Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server Management Studio allows debugging of SQL (breakpoints and stepping through the SQL). It is possible to step in to a stored proc being called and to see a call stack. However I cannot determine how to set a breakpoint on a stored proc that will be called from those statements, that is, I can set a breakpoint on the top level SQL statements that I run that call on to sub-procs, but any breakpoints in the sub-procs are not hit.

UPDATE:

When debugging the 'breakpoints' view shows a warning symbol against the problematic breakpoints with a tooltip text of 'The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded'.

If however I step into a stored proc with the debugger and place breakpoints on the view that it has opened then the breakpoints are bound correctly. So my question is, how do I get to to a view that allows me to bind breakpoints to stored procs correctly? It seems that right clicking on a stored proc and selecting 'Modify' gets you to a view that is not bound to the debugger.

share|improve this question
1  
Be VERY careful debugging against SQL Server. If you debug against a CLR object and pause the debugger you'll pause the entire SQL instance. Just an FYI. –  mrdenny Jan 19 '12 at 3:02

2 Answers 2

I'm not sure how helpful this answer is for you, but another option is to open Visual Studio rather than SQL Management Studio, and open the View->'Server Explorer' option. Add a new connection to the database in question, then drill through to the stored procedure of interest, then right click and select 'Step-Into Stored Procedure', which will prompt you for parameters and then allow you to place breakpoints as the procedure is running. Another option is in SSMS you can place a breakpoint on the actual 'EXEC ' line, and when it hits that breakpoint step into it via the debug menu. Then you can arbitrarily place breakpoints inside the procedures code.

share|improve this answer

It is because you need to create the breakpoint using the database name and object id rather than setting a breakpoint in a random SQL file which is what you get by choosing modify.

See:

https://sqlserverfunctions.wordpress.com/2014/09/12/setting-breakpoints-in-ssms/

Ed

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.