How to: Debug T-SQL Using the SQL Server Management Studio

How to: Debug T-SQL Using the SQL S...

Switch on low bandwidth view

Collapse All

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Visual Studio Debugger

How to: Debug T-SQL Using the SQL Server Management Studio

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default.

You can do all your T-SQL development except debugging inside the SQL Server Management Studio. For complex T-SQL development this is an option to consider.

This procedure assumes you have already developed and successfully deployed a T-SQL object to the database, and you are now ready to debug it. This procedure also assumes you are debugging locally, but the same basic procedure can be done when debugging remotely.

Note:
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To run a test script from SQL Server Management Studio

Open Visual Studio and establish a connection to the database. For more information see How to: Connect to a Database.
Attach the debugger to the SQL Server process by doing the following.
1. From the Tools menu, select Attach to Process.
2. In the dialog box, find sqlservr.exe in the list of available processes, and click to highlight.
3. Press the Select button to open the Select Code Type box.
4. Select the Automatically determine the type of code to debug option, and click OK.
5. Click the Attach button.
In Server Explorer, open the database object to be debugged and set breakpoints.
In the SQL Server Management Studio, run a script that will test the database object. You cannot set breakpoints in this script, but the script will cause the database object to execute, and its breakpoints to be hit.
The code for the object will appear in Visual Studio with a yellow arrow in the left gray margin indicating the statement about to be executed. You may now use most of the familiar debugger features.
To finish debugging, press F5 or Start. The code will execute and exit the debugger.

Other Resources

Common Procedure Reference

Tags

: Add a tag

Community Content

Add new content

Annotations

Step 3 is misstated

msorens | Edit | Show History

I am using VS2008 Pro and SSMS 2005 Express.
While this page is useful it is not quite accurate, at least in my environment. I first tried opening the database object as Step 3 suggested. While that opens the script it is some type of copy, not the "real thing": although I could set breakpoints, neither invoking Execute from the context menu of the Server Explorer nor running a query in Sql Server Management Studio Express hits those breakpoints. Steps 3 to 5 should say:

3. In Server Explorer, right-click the database object to be debugged and select Step Into Stored Procedure. Set all the desired parameters and press OK.
4. This opens up the script in the VS2008 debugger, with the code pointer at the first executable line. Set breakpoints and debug your T-SQL as you would any other file in VS2008.