SQL Server 2008 TSQL Debugger is back in SSMS

By: Arshad Ali | Read Comments (2) | Print Arshad is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft. Related Tips: 1 | 2 | More

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.

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.

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 the sysadmin 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.

Next Steps

• Review the Debugging SQL Server CLR functions, triggers and stored procedures tip.

• Review “Using the Transact-SQL Debugger” on MSDN.

• Happy T-SQL coding and debugging. :)

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code