Why are breakpoints not being hit in SQL Server Management Studio?

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.

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

StackExchange Database Administrators Q#10630, answer score: 4