Transact-SQL Debugger for SQL Server 2008 – Part 3

  • Article

In part 3 of working with the debugger, I’ll talk about how to set breakpoints and the trick to setting them in stored procedures and triggers.

This 3 part series covers:

  1. Basic debugging
  2. Configure remote debugging & best practices
  3. Debugging triggers and stored procedures with breakpoints

The trick with setting breakpoints in called stored procedures and triggers is that they need to be set within the temporary file that the debugger creates for the object.

In this scenario, I’m going to call the HumanResources.uspUpdateEmployeeHireInfo stored procedure in the 2005 version of AdventureWorks. This stored procedure updates two tables: Employee and EmployPayHistory both in the HumanResources schema.  The goal of the example is to set a breakpoint in the trigger for the Employee table and then show how the debugger catches it the next time through.

The obvious thing that you would try is to Modify the trigger and then toggling the breakpoint [F9] on the line you want to stop at. The problem is that the debugger has no context. so the approach shown below won’t work.

Debugging30 - No good with Modify

What you need to do is step into the stored procedure or trigger you want to break on and set the breakpoint in the temp file the debugger creates.

Let’s step through the example starting with the script to call the stored procedure.

Debugging31 - Start script

I’ve toggled the breakpoint to demonstrate how breakpoints can be set in a script of batch statements. When U press [ALT]+[F5] twice to start and continue debugging, you’ll see the debug stops at the break point in the script.

Debugging32 - Break in script

You will now want to Step Into the stored procedure with [F11]. The debugger loads up the stored procedure into a new editor window and stops at the first executable line.

Debugging33 - Now in SP

Notice in the Call Stack window that procedure name is followed by (SQL1\SQL2K8.AdventureWorks) indicating the server instance and database name context for the debugging session. If you hover over the statement, you will see a tool top showing the parameters with values called and the current line.

Step into [F11] again moves to the BEGIN TRY statement. [F11] again to the BEGIN TRANSACTION statement. One more [F11] positions you on the UPDATE statement for the Employee table.

Debugging34 - ready to go into update

Step into [F11] the UPDATE statement causes the debugger to load the update trigger for the Employee table.

Debugging34 - In Update trigger

At this point, we can now set a breakpoint [F9] on the UPDATE statement for the trigger.

Debugging36 - Set bp in update trigger

In the breakpoint window for this example, you’ll notice something interesting.

Debugging37 - object id set

You’ll see that the debugger is actually showing the object_id for the trigger name. This way the the debugger can keep track of the breakpoint for future sessions.

For now, lets press [ALT]+[F5] to let the debugger continue. Lets test out the breakpoint for the trigger.

First toggle off the breakpoint [F9] in line 11 of the calling script. Then press [ALT]+[F5] to start the debugger. You’ll see that the breakpoint is still present in the Breakpoint Window.

Debugging38 - Start again to demo break

One more continue [ALT]+[F5] stops right where you want!

Debugging39 - Stopped where we needed

Let’s finish the debug session with the Continue command [ALT]+[F5].

At this point, I should point out that breakpoints are persisted with the database solution/project. If you didn’t have solution, any breakpoints you defined will go away when you close SSMS. To learn more about solutions, you can refer to the help topic – Using Solution Explorer.

Technorati Tags: SQL Server Mangement Studio,SQL Server 2000,Transact-SQL Debugger

TENA_blgr3_seeme