Setting breakpoints in SSMS is difficult and a bit of a pain, if you use the object explorer to open a procedure or function and set a breakpoint, SSMS will happily show it as being set but when you debug the code, you get nothing. The reason is that what you need to set the breakpoint on is a temporary file which only SSMS knows about. The file you have set the breakpoint, although looks right is like creating a csharp file on your desktop with the same text as in a code file in a solution, setting a breakpoint and then running your solution and wondering why the debugger didn’t stop in the right place.
This shows a breakpoint which looks like it is set but it is not actually set:
Breakpoint will not be hit, notice the name of the file SQLQuery1.sql.
There are a couple of ways to set the breakpoint in the actual file, the first is to debug your code and step into or F11 into the place where you want the breakpoint, when you do that, SSMS opens the code file for you but instead of using a name like SQLQuery1.sql it opens a mssql:// path:
If you notice the name of the tab it shows the file name as the cryptic: “mssql://WIN-UJM5DI37BMD/test/?/=261575970”.
The file name may well look cryptic but it is quite simple, it is the name of the instance then the database, in this case “test”, followed by the object id of the item you want the breakpoint set it.
If we look at the breakpoints window, we can see the same file name and also do some cool stuff like set conditional breakpoints or jump to the breakpoint. This takes you to the file where you can set more breakpoints of course. To get to the breakpoint menu, go to Debug –> Windows –> Breakpoints. You need to have at least one active script window open, I am not sure why, you just do.
Setting your own breakpoints
To set your own breakpoints either get SSMS to show you the debuggable version of the code or add the breakpoint manually, I normally get SSMS to set one and then copy it and modify it to get it set to the object and location I like. To set one manually, in the breakpoint window, choose New —> Break at Function and enter the filename and line and column (I know, more pain!). If you try to set a breakpoint at an invalid location you will get a failure message.
I hope this makes debugging in SSMS easier, if anyone has any other tips please share!