T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy

You can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5.

In the image below I am executing a batch in debug mode; you can notice several windows on the bottom though details of Locals window are only visible which displays information about the local variables in the current scope of the T-SQL debugger.

Let’s see another example. The script for this is provided in the below table, a procedure calls another procedure, so during execution if you look at the Call Stack window you will get the current execution location, and information about how execution passed from the original editor window through any T-SQL routines (functions, stored procedures, or triggers) to reach the current execution location as shown in the image below.

Script #1: Create a procedure which calls another procedure

CREATE PROCEDURE PROC1 AS BEGIN PRINT 'A' PRINT 'B' EXECUTE PROC2 END GO CREATE PROCEDURE PROC2 AS BEGIN PRINT 'C' PRINT 'D' END GO EXECUTE PROC1 GO

---

Note

SQL Server Management Studio must be running under a Windows/SQL Server account that is a member of thesysadmin fixed server role or else you will get an error something like this.

If you are running the T-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions by using the Windows Firewall Control Panel application on both computers, for more details click here.

Microsoft recommends that T-SQL code should be debugged on a development/test server and not on a production server because first it requires the member to be part of sysadmin fixed server role to debug and second it may hold resources for longer period while you debug and investigate.

---

Limitation

If you are connecting to SQL Server 2005 even from SQL Server 2008 SSMS, you would not be able to debug your routines and get this error, it happens because T-SQL debugger includes both server-side and client-side components. So in a nutshell, it works with SQL Server 2008 only so far.

The T-SQL debugger does not support the Microsoft Visual Studio features of setting breakpoint conditions or hit counts.