How to: Debug Database Objects

A database unit test consists of the following:

  • One or more test conditions, which are written in C# or Visual Basic. To debug test conditions, follow the procedure for debugging a unit test as described in How to: Debug while a Test is Running.

  • One or more (Transact-SQL) T-SQL scripts that run on objects in the database that you are testing. You cannot debug these T-SQL scripts.

The procedures in this topic describe how to debug particular database objects, such as stored procedures, functions, and triggers. To debug a database object, follow these procedures in this order:

  1. Enable SQL Server debugging on your test project.

  2. Add breakpoints to your T-SQL script.

  3. Debug your database unit test. In this procedure, you run the test in debug mode.

To enable SQL Debugging on your test project

  1. Open Solution Explorer.

  2. In Solution Explorer, right-click the test project, and click Properties.

    A properties page that has the same name as the test project opens.

  3. On the properties page, click Debug.

  4. Under Enable Debuggers, click Enable SQL Server debugging.

  5. Save your changes.

To set the execution context timeout to enable debugging for your test project

  1. On the File menu, point to Open, and click File.

  2. Browse to the folder that contains your test project, and double-click the app.config file.

    The app.config file opens in the editor.

  3. Modify the ExecutionContext node to add a command timeout, as in the following example:

    <ExecutionContext CommandTimeout ="300" Provider="System.Data.SqlClient" ConnectionString="Data Source=TargetServerName\TargetInstanceName;Initial Catalog=TargetDatabaseName;Integrated Security=True;Pooling=False" />
    
  4. Save your changes.

  5. Rebuild your database unit test project.

Important noteImportant Note:

If you do not rebuild your project, the changes that you made to app.config will not be applied when you run your database unit tests, and debugging will fail.

To add breakpoints to your T-SQL script

  1. On the View menu, open Server Explorer.

  2. Under Data Connections, expand the node of the database that you want to test.

  3. If a small red 'x' appears next to the icon of the database, the connection to the database is closed. In this case, right-click the database, and click Refresh. You might have to supply credentials to open the connection to the database.

  4. Expand the Views, Stored Procedures, or Functions node to find the object that you want to debug.

  5. Double-click the object that you want to debug.

  6. Click the gray sidebar to set a breakpoint.

To debug your database unit test

  1. Open the Test View window.

  2. Click the test whose T-SQL script exercises the database object in which you set breakpoints.

  3. On the toolbar of the Test View window, click Debug Selection.

    The test runs in debug mode until a breakpoint in the database object is encountered.

  4. (Optional) To open another debug window, open the Debug menu, point to Windows, and click Breakpoints, Output, or Immediate.

See Also

Concepts

Terminology Overview of Database Edition

Other Resources

T-SQL Database Debugging