SQL Server 2008 TSQL Debugger is back in SSMS

By:   |   Updated: 2009-02-25   |   Comments (3)   |   Related: 1 | 2 | > Debugging


Problem

If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. Starting with SQL Server 2005, Query Analyzer and Enterprise Manager had been clubbed together as SQL Server Management Studio (SSMS). Though this single interface has simplified working with SQL Server, one major drawback was, it does not allow you to debug a routine from there. For that purpose you needed Visual Studio (Enterprise and Professional) edition installed, on your development machine, which allowed you to debug a routine. The requirement to install Visual Studio is something that database developers and DBAs would be reluctant to do as it requires additional funds for a Visual Studio license and puts additional pressure on the physical box after installation.

Solution

Debugging capability in SSMS was a long sought feature by users ( http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550 , http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124613 ) and finally the Microsoft SQL Server team decided to provide this feature in SQL Server 2008. With this feature you can navigate through your code, line by line, watch the current state of variables in the program, an output window where you can observe what the code is doing, error windows, the ability to navigate backward and forward through each line, observe system objects such as the call stack and threads, not only this you can even place "breakpoints" and then tell the debugger to run the code up to that point and then stop.


Example

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.

T SQL img1

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 

T SQL img2


Note

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

T SQL img3

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.

T SQL img4

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

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-02-25

Comments For This Article




Wednesday, August 15, 2012 - 4:30:18 PM - Shekinah Back To Top (19054)
you should try test drvien development you'll code faster by writing unit tests first then writing the code to make the tests pass it's hard getting used to at the beginning but it'll save u hours of debugging later.if you do TDD properly you might never need to use a debugger

Friday, August 5, 2011 - 8:28:44 AM - Rick Back To Top (14317)

This is fairly useless for your run-of-the-mill SQL developer since nobody is going to hand out the sysadmin server role...

Microsoft needs to make this so it will function without sysadmin. If the concern is that this allows someone to step into what should be a protected system SP or Function then implement protection in the debugger to prevent that rather than requiring the sysadmin role.


Thursday, February 26, 2009 - 11:42:40 AM - jerryhung Back To Top (2826)

I must say, while SSMS 2008 has many cool GUI features - IntelliSense, Debugger

They are useless if they ONLY work with SQL Server 2008, which will probably take 10 years for everybody to be on it

= 5 years until I can use native IntelliSense for EVERY SQL Server at work

= I will stay with Red Gate SQL Prompt for now

 

As for Debugger, PRINT statements :(















get free sql tips
agree to terms