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

Topic does not applyTopic does not applyTopic does not applyTopic does not apply

Standard

Topic does not applyTopic does not applyTopic does not applyTopic does not apply

Pro and Team

Topic appliesTopic appliesTopic appliesTopic applies

Table legend:

Topic applies

Applies

Topic does not apply

Does not apply

Topic applies but command hidden by default

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.

NoteNote:

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

  1. Open Visual Studio and establish a connection to the database. For more information see How to: Connect to a Database.

  2. 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.

  3. In Server Explorer, open the database object to be debugged and set breakpoints.

  4. 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.

  5. 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.

  6. To finish debugging, press F5 or Start. The code will execute and exit the debugger.

See Also

Other Resources

Common Procedure Reference
Tags :


Community Content

msorens
Step 3 is misstated
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.
Tags :

msorens
More options for debugging SQL Server
As I was trying to figure out how to make this work (which led to my earlier post), I found "Niels SQL Server Blog" at
http://sqlblogcasts.com/blogs/nielsb/archive/2004/08/04/SQL-Server-2005-Debugging.aspx to be particularly helpful. It discusses three techniques for Server debugging, including the Server Explorer technique discussed here.
Tags :

Page view tracker