Debugging SQL Database Objects
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer

Debugging SQL Database Objects

This topic applies to:

Visual Studio Edition

Visual Basic




Visual Web Developer



















Database objects can now be written in T-SQL or in a common language runtime (CLR) language. This section describes how to debug SQL database objects. There are topics for each database object type: one for the SQL/CLR version and, if applicable, one for the T-SQL version. Some types of SQL/CLR database objects cannot be written in T-SQL.

The development life cycle for database objects includes coding, deployment to a database, unit testing, application testing, and debugging. Prior to SQL Server 2005, all database objects were written in T-SQL. It was common to go through the entire life cycle using the Query Analyzer tool (which included a T-SQL debugger), except for application testing, which was done using Visual Studio. In SQL Server 2005, Query Analyzer has been replaced by the SQL Server Management Studio. Because of the new capability of creating SQL/CLR database objects, debugging is done inside Visual Studio, rather than in SQL Server Management Studio, which does not include a debugger.

This section focuses on development using the SQL Server project type of Visual Studio. Developing SQL/CLR objects is easy using a SQL Server project type: coding, deployment, unit-testing and debugging are integrated into a single IDE, all automated into a simple process. T-SQL objects can also be developed in this project type. For more information see Creating SQL Server 2005 Objects in Managed Code.

Other ways to code database objects include:

  • T-SQL objects can be coded and deployed in Visual Studio using a Database project type. This project type provides design-time tools for creating database objects. You can also do SQL Server administrative tasks such as creating tables, views, and indexes with this project type. For more information, see Overview of Visual Database Tools.

  • Complex new or legacy T-SQL objects can be written (or modified), deployed and unit-tested in SQL Server Management Studio. If debugging is required, you use Visual Studio. If most of your database development work is done in T-SQL, and you already have experience using Query Analyzer, this may be a good option for you.

  • Visual Studio isn't actually required for developing SQL/CLR database objects; you could also use a text editor or 3rd party IDE for development, and deploy the objects using the SQL Server Management Studio. This option requires more knowledge of SQL Server than the option of using Visual Studio and will likely be less productive.

Deployment can be done in two ways:

  • Once you have created a SQL/CLR object, you can deploy it using T-SQL commands in the SQL Server Management Studio.`

  • T-SQL objects can also be deployed via SQL Server Management Studio. In this scenario development and deployment are a single step, because you write a script that will create the object, and then you run the script.

Unit testing and debugging can be done several ways.

  • Existing database objects can be debugged using Visual Studio outside of any project. Using Server Explorer you can step into a database object. For more information see How to: Step into an Object Using Server Explorer.

  • You can unit test objects by running a T-SQL script in the SQL Server Management Studio which will execute the object, and you can debug the object from Visual Studio. For more information see How to: Debug T-SQL Using the SQL Server Management Studio.

  • You can run a test script from a Visual Studio 2005 Database project.

Once the database object has been unit tested, applications that call the object must be tested. This process is described in Multi-tier Application Database Debugging.

In This Section

Overview of Visual Database Tools

Describes limitations on the use of SQL Debugging features.

Common Procedure Reference

Describes procedures that occur in many different scenarios and samples

T-SQL Database Debugging

Shows how to debug various kinds of T-SQL database objects: stored procedures, triggers, user-defined functions, and extended stored procedures.

SQL CLR Database Debugging

Shows how to debug various kinds of CLR database objects: stored procedures, triggers, user-defined scalar functions, user-defined table-valued functions, user-defined aggregates, and user-defined types.

Related Sections

Overview of Visual Database Tools

Describes restrictions and limitations on the use of SQL Debugging features.

Setting Up SQL Debugging

Lists software requirements for SQL debugging (for the server and workstation), instructions for enabling SQL debugging, installing, configuring, and troubleshooting setup.

Multi-tier Application Database Debugging

Describes the necessary setup procedures, and provides a sample that shows how to debug a multi-tiered database application.

Monitoring the Flow of SQL Debugging Information

Suggests using the Network Monitor (NetMon) or a similar tool to monitor the flow of SQL debugging information and provides a pointer to the Network Monitor documentation.

See Also

© 2016 Microsoft