Skip to content

how-to-debug-database-objects.md

Latest commit

 

History

History
96 lines (59 loc) · 4.3 KB

how-to-debug-database-objects.md

File metadata and controls

96 lines (59 loc) · 4.3 KB
Raw
title Debug Database Objects
description Find out how to debug stored procedures, functions, and triggers. See how to turn on debugging, set breakpoints, and run a SQL Server unit test in debug mode.
author dzsquared
ms.author drskwier
ms.reviewer randolphwest
ms.date 09/09/2025
ms.service sql
ms.subservice ssdt
ms.topic how-to
ms.custom
sfi-ropc-nochange

How to: Debug database objects

A SQL Server unit test consists:

  • Unit test code written in C# or Visual Basic. This code, which is generated by the SQL Server Unit Test Designer, is responsible for submitting the Transact-SQL script that forms the body of the test.

  • 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 (Visual Studio 2010) or How to: Debug while a Test Is Running (Visual Studio 2012).

  • One or more Transact-SQL scripts that run on objects in the database that you're testing. You can't debug these Transact-SQL scripts.

The procedures in this article describe how to debug particular database objects, such as stored procedures, functions, and triggers in the database you're testing. To debug a database object, follow these procedures in this order:

  1. Enable SQL Server debugging on your test project.
  2. Enable application debugging on the SQL Server instance that hosts the database you're testing.
  3. Set breakpoints in the Transact-SQL script of the database objects you're debugging.
  4. Debug your unit test. In this procedure, you run the test in debug mode.

Enable SQL Debugging on your test project

  1. Open Solution Explorer.

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

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

  3. On the properties page, select Debug.

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

  5. Save your changes.

Set an increased execution context timeout to enable debugging for your test project

  1. On the File menu, point to Open, and select 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 unit test project.

Important

If you don't rebuild your project, the changes that you made to app.config aren't applied when you run your unit tests, and debugging fails.

Add breakpoints to your Transact-SQL script

  1. On the View menu, open SQL Server Object 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 select 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. Select the gray sidebar to set a breakpoint.

Debug your SQL Server unit test

  1. Visual Studio 2010, open the (Test > Windows) Test View window. In Visual Studio 2012, open the Test Explorer window.

  2. Right-click the test whose Transact-SQL script exercises the database object in which you set breakpoints and select Debug Selection.

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

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

Related content