How to: Debug with a SQL Server Database Project or Server Project

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

The following procedure describes how to debug Transact-SQL using the Visual Studio SQL Server Database project or Server project type. For information about debugging Transact-SQL from C++, Visual Basic, or C# projects, see Debugging Multi-tier Database Applications.

While in the context of a SQL Server Database project or Server project, any time you initiate debugging, the debugger will always begin debugging the Transact-SQL code in the active Transact-SQL query window. If you do not want to debug the entire script, highlight only a subset of the Transact-SQL code in the query window before you start debugging.

To debug database objects such as stored procedures and triggers, you need to step into those objects from Transact-SQL code in the active Transact-SQL query window. For example, if you want to debug a stored procedure: set a breakpoint on an EXECUTE statement that calls that stored procedure, start debugging on the same Transact-SQL query window, and then step into it by clicking Step Into on the Debug toolbar.

To debug a Transact-SQL script on the active Transact-SQL query window

1. Confirm your computer has been set up for Transact-SQL debugging. For more information, see How to: Enable Transact-SQL Debugging.

2. Start Visual Studio, and create a new SQL Server Database project or Server project.

3. Open a Transact-SQL script that you want to use to begin debugging. You can also open a new .sql file that is or is not associated with the current project.

   1. To add a new Transact-SQL script to the project: right-click the project name in Solution Explorer, click Add, and then click New Item. In the Add New Item dialog box, select Script (located in the User Scripts template, under the Database Project node). Then click Add.

   2. To open a new Transact-SQL query window not associated with the project, see How to: Start the Transact-SQL Editor and How to: Connect to a Database in the Transact-SQL Editor. Note: you will not be able to debug Transact-SQL if you do not first open a SQL Server Database project or Server project.

4. After the Transact-SQL query window is open, begin writing your Transact-SQL code if you have not already done so. Continue to the next step when you are ready to begin debugging.

5. Place breakpoints in the Transact-SQL code by clicking in the left margin of the Transact-SQL query window on the lines of code where you want to break execution.

6. Start debugging the active Transact-SQL query window by pressing F5 or opening the Debug menu and clicking Start Debugging.

7. Use the Step Into, Step Over, or Step Out buttons on the Debug toolbar to navigate the debugger through your Transact-SQL script. You can also press F5 to continue execution until the next breakpoint or end of the script.

8. Use the Locals and Watch window to examine the state of the code. For more information, see Transact-SQL Object Debugging Using the Locals and Watch Windows.

9. When you are finished and ready to stop debugging, press CTRL+ALT+BREAK or click Stop Debugging on the Debug toolbar.

To debug Transact-SQL database objects that have been deployed to the server

1. Confirm your computer has been set up for Transact-SQL debugging. For more information, see How to: Enable Transact-SQL Debugging.

2. Start Visual Studio, and create a new SQL Server Database project or Server project.

3. Open a Transact-SQL script that you want to use to test your Transact-SQL database objects with. You can also open a new .sql file that is or is not associated with the current project.

   1. To add a new Transact-SQL script to the project: right-click the project name in Solution Explorer, click Add, and then click New Item. In the Add New Item dialog box, select Script (located in the User Scripts template, under the Database Project node). Then click Add.

   2. To open a new Transact-SQL query window not associated with the project, see How to: Start the Transact-SQL Editor and How to: Connect to a Database in the Transact-SQL Editor. Note: you will not be able to debug Transact-SQL if you do not first open a SQL Server Database project or Server project.

4. After the Transact-SQL query window is open, begin writing your Transact-SQL testing code if you have not already done so. The only way you can debug Transact-SQL database objects is to step into them the active Transact-SQL query window. Write Transact-SQL code in your test script that will cause that Transact-SQL to be executed.

   1. To debug a stored procedure, add an EXECUTE statement to your test script that will use the corresponding stored procedure.

   2. To debug triggers, user-defined functions and other programmable Transact-SQL database objects, write Transact-SQL code in your test script that will cause that code to be executed.

5. Place breakpoints in the Transact-SQL test script by clicking in the left margin of the Transact-SQL query window on the lines of code where you want to break execution. To debug the Transact-SQL database objects that have been deployed to the instance of SQL Server, place the breakpoints just before the Transact-SQL code that will cause those objects to be executed.

6. Before starting the debugger, click your Transact-SQL test script to make sure that it is the active window in Visual Studio.

7. Start debugging your Transact-SQL test script by pressing F5 or opening the Debug menu and clicking Start Debugging.

8. After the debugger breaks execution on the breakpoint, press F8 or open the Debug menu and click Step Info to step the debugger into the Transact-SQL code of the Transact-SQL database object. Once the debugger steps into the Transact-SQL code of the database object, Visual Studio will open a new Transact-SQL query window to display the corresponding Transact-SQL code.

9. Once the debugger has stepped into the Transact-SQL database object, you can set additional breakpoints and use the Step Into, Step Over, or Step Out buttons on the Debug toolbar to navigate the debugger through the Transact-SQL code. You can also press F5 to continue execution until the next breakpoint or end of the code.

10. Use the Locals and Watch window to examine the state of the code. For more information, see Transact-SQL Object Debugging Using the Locals and Watch Windows.

11. When you are finished and ready to stop debugging, press CTRL+ALT+BREAK or click Stop Debugging on the Debug toolbar.

See Also

Other Resources

Creating SQL Server 2005 Objects in Managed Code

Overview of Visual Database Tools

Debugging Transact-SQL Database Objects