The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
We’re back. Time for day 9. We return to the subject of debugging. Yesterday we went over inspecting variables in flight and setting breakpoints, labels, and data tips. Today we’re going to talk about those subjects from the standpoint of team debugging.
As covered yesterday breakpoints provide a lot of options great for tackling large complex queries and procedures. You could spend quite a bit of time getting a specific query set up just the way you need it to analyze inputs, outputs, and logic with multiple breakpoints and conditions. If you were working in a team environment and the task of debugging a specific query was given to multiple members of the team – they wouldn’t want to have to rebuild that debugging session from scratch. This problem could be the same even if you were going to continue the debugging session yourself but in a different environment with a different install of SSMS. Such as what could occur when moving scripts from a dev environment to a stage or a certification type environment.
Enter the ability to export and import the meta data related to breakpoints and data tips etc.
To demonstrate I opened dbo.ufnLeadingZero inside AdventureWorks2012 and set up a breakpoint with a condition to only hit when value is less than 1000. It also has a label identifying the breakpoint. I then exported the breakpoint out by right clicking the breakpoint and selecting export. The file exports as a straight forward XML document that is pretty easily understood from your favorite text editor.
It is also possible to export multiple breakpoints at once from the breakpoint window. You can even create a search filter against all existing breakpoints and then export only the subset that matches your criteria by clicking the little breakpoint with a save/diskette option on the breakpoint window toolbar.
Importing an existing breakpoint into SSMS is as simple as opening the breakpoint window and clicking ‘Import Breakpoint’ from the toolbar. It’s the little icon next to the export toolbar button. Once you import you’ll see all options and meta data is intact from initial setup.
A minor gotcha with exporting / importing breakpoints is that the file name and location on the file system has to remain consistent. If the script to be debugged is in a different location on the target SSMS instance you’ll get a popup stating ‘Unable to go to breakpoint’.
To avoid this error either ensure the sql script files being debugged are located in the same file path on both instances or plan on editing the exported breakpoint XML file to reflect the new location.
As mentioned briefly yesterday you can view the values of variables with data tips. Generally speaking ‘Data Tips’ are simply the pop up windows that appear when you hover an item of interest with your mouse. When dealing with data tips and debugging in SSMS specifically; data tips refer to the ability for you to add custom information to a specific code point.
Like breakpoints these data tips can be exported for use on another system or by another member of your development team. Before we talk about that however – let’s see a “data tip” in practice. I’ve written a simple loop to incrementally add 1 to a couple integers. Running the query in debug mode I can step to that point and then hover the value with my mouse to bring up it’s related data tip.
Notice the little pin icon? If you click the pin, it will keep the data tip visible on that line even after you’ve moved off it with your mouse or stepped past the line with the debugger. It will update as the value represented by the variable pinned changes. You can even add your own comment or label by clicking the little down/up arrow below the pin icon.
With a bunch of data tips set up on your script – you can now export them and share with other environments or team members. Hit the debug menu item and select export / import data tips. The exported file, like the breakpoint export, is a simple XML document that contains the basic information necessary to persist the tip elsewhere. Also, like the breakpoint export, when imported it must line up with the physical file location and name of an existing file to work.
All the debugging we’ve covered so far has been on a development box where SSMS and the SQL Server instance exist on the same box. Tomorrow we’ll talk about the ins and outs of debugging a remote server. ‘Til then!!