During Tech-Ed (read my 3 part series here – Part 01, Part 02, Part 03), I learnt about some of the cool new T-SQL debugging enhancements in the next public release of Microsoft SQL Server, SQL 11 (Code Named: “Denali”). I am currently summarizing these enhancements, and will finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.

We are currently looking at debugger enhancements related to breakpoints. Last week, we saw how to setup conditional breakpoints, label them & search for them. In today’s environment, it is common to have a team of individuals working on a particular feature/enhancement. Such a team of individuals would end up debugging a piece of code together, or on behalf of one another. In production support, with complex systems and interfaces, it is common to have an issue that continues to linger for multiple days.

When debugging takes time, it is beneficial if the debugging effort is split across multiple sessions or multiple machines. With SQL 11 (“Denali”), this is now possible.

Exporting Breakpoints

Let’s continue our debugging example from last week. For your reference, the script being used can be found below. We had placed conditional debug points at each PRINT statement in the script – one was conditional, while the other was a hit-count based breakpoint.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Now, let us see how to export breakpoints from one SSMS session to another.

Once the script file has been saved to any location of your choice, click on the “Export” button on the toolbar of the “Breakpoints” window
In the File->Save dialog, select a location and file name of your choice to export the breakpoints

Importing Breakpoints

Now that we have exported breakpoints to a file, let’s see how we can import them into another SSMS session.

Launch SSMS and open the query to debug. Launch the breakpoints window (Debug –> Windows –> Breakpoints, or Ctrl + Alt +B) and click the Import button:
In the File->Open dialog box, choose the breakpoints file saved above
Notice that the breakpoints are imported and applied successfully such that the IDE is now ready for debugging

Limitation

As with any enhancement, there are always things that can be improved upon after the fact. The only major limitation that I see with exporting & importing breakpoint is:

• The name & location of the script file must be same as that when the breakpoints are exported
• Therefore, if when the breakpoints were exported, the script file was at “E:ScriptsBreakpoints.sql”, I cannot change it to be “E:DevelopmentBreakpoints.sql” for the import

However, I do not see this as a limiting factor because in an heterogeneous environment with a central IT team, developers tend to work on similar/same code paths and directory structures.

For those who missed the previous post on debugging enhancements, please click here.

Until we meet next time,

Be courteous. Drive responsibly.