FoxTricks

Debugging TSQL Code on SQL Server 2008

SQL Server 2008 once again comes with a TSQL Debugger (which is really quite good). The only condition for using it is that the code is available in a non-encrypted form on the database. To assure this, you have 2 options:

You can check if your code is decrypted by typing sp_helptext 'p_myproc' where the code of your procedure should appear in the results window.

Setting up your PC for Debugging

The first time you start debugging a TSQL statement, Management studio pops up a request to alter your firewall settings to allow debugging. Unfortunately this did not work at all in my case. So I had to dive in and make the required changes.

A number of Firewall settings have to be done to enable TSQL Debugging on your pc.

Start the Firewall tool:

Image003

In the firewall application a number of changes need to be made:

Exclude TCP Port 135

Right click on the Inbound rules node and select 'New Rule...'

Image010

Then select 'Port' and click Next

Image011

Now enter the TCP Port 135 as excluded and click Next

Image012

In the next dialog you make sure 'Allow the Connection' is selected and click Next

Image014

On next pane you specify which networks can access this port. For security reasons it is best to disable "Public" networks. Select the options as shown below and click next.

Image016

In the Naming dialog enter something like "TSQL Remote Debugging: Allow DCOM" and click finish:

Image023

Exclude sqlservr.exe

Again right click on the "Inbound rules" node and select New Rule. This time we take the option "Program" and click next.

Image024

In the next dialog we will select the path where sqlservr.exe is installed (This depends on the name of your SQL Server instance. You can search for sqlservr.exe in the "C:\Program Files" folder). Click next when Done.

Image026

Again select the "Allow the connection" option and click Next. In the next Dialog once again disable "Public" networks. Click next and enter as name "TSQL Remote Debugging: sqlservr.exe"

Exclude UDP Ports 4500 and 500

Follow the same process as for "Exclude TCP Port 135" but enter the ports "500, 4500" in the "Protocols and ports" dialog

Image029

Proceed the same as in "Exclude TCP Port 135" and enter "TSQL Remote Debugging: IpSec" as name

Exclude ssms.exe (SQL Server Management Studio)

The next application to exclude is management studio. Follow the same approach as "Exclude sqlservr.exe" but search for ssms.exe (C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe) . Proceed identical as "Exclude sqlservr.exe" but enter "TSQL Remote Debugging: ssms.exe" as name

Exclude msvsmon.exe

This one is a bit special because it exists in a 32-bit and 64-bit flavor. For convenience we will exclude them both.

Proceed the same as for sqlservr.exe and ssms.exe and exclude the following 2 applications

And the second rule

This concludes the firewall configuration required to perform TSQL Debugging.

Using the TSQL Debugger

Open management studio and enter the SQL Statement you wish to debug and click on the "Start Debugging" option under the "Debug" menu. (or press Alt-F5)

Image032

This will start your Query and stop processing on the first statement it encounters. This you can see from the yellow arrow in the left margin.

Image038

To continue we have a number of new options in the debug menu

Image039

When you meet a piece of Dynamic SQL to be executed the debugger will open a new window where you can step through the generated SQL Code (which is super nice)

To set an extra breakpoint (ie: to press on with Alt-F5 to a certain line of code you click in the margin of the code window or press F9 which will display a red dot. If you then hit Alt-F5 the execution will stop at this red dot as shown with the yellow arrow:

Image043

Another convenient way to "Catch up" to the current line is by right clicking and selecting the "Run to cursor" option. This will advance the debugger to your current line.

Image046

Watch windows

To help you debug your code there are a number of help windows available.

The first one is the "Locals" window, this window displays the content of the parameters and variables currently in the scope of the code.

Image040

The next dialog is the Quick Watch window. If you have a "complex" expression for which you want to see the results of an extract you can do this with a quick watch. Select the variables to evaluate and right click. Select the quick watch option which will open the following window where you can enter variables, parameters, and the system functions that have names that start with @@.

Image044

Viewing the content of Temp tables

The TSQL debugger does not provide an "out of the box" way to view the content of "local" temp tables. For this I built a stored procedure which you can run from any thread. The code is available on https://github.com/FilipDeVos/sp_select.

Notes:

Something you should not forget is that the output of your "Original" query is still being printed to the results window of the window where you started debugging. An easy layout to work with the debugger is by making a "Vertical Split" which enables you to see the output together with the debugged statements

Image050