4

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.

redcalx
  • 383
  • 3
  • 5
  • 11

2 Answers2

4

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

Ed Elliott
  • 775
  • 5
  • 12
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.

NickPeterson
  • 21
  • 2