Setting breakpoints in SSMS

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 never be hit :(

breakpoint will never be hit 😦

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:

breakpoint will always be set :)

breakpoint will always be set 🙂

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.

breakpoint window


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.


Use the strange naming to set a breakpoint

Use the strange naming to set a breakpoint

I hope this makes debugging in SSMS easier, if anyone has any other tips please share!

One comment

  1. Tim LaVenice · May 4, 2016

    Thanks for this super helpful post Ed! One thing I notice, which through me off when trying this, is that your “New Breakpoint” screenshot shows the function as mssql:// and not mssql:://. When I added the second colon, this worked like a charm!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s