Debugging SQL Database Objects

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default.

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. Before 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 by 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 performed inside Visual Studio, instead of 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 the following:

• 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, 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 performed in T-SQL, and you already have experience with Query Analyzer, this might be a good option for you.

• Visual Studio is not actually required for developing SQL/CLR database objects; you could also use a text editor or third-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:

• As soon as 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 through 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 Transact-SQL Using the SQL Server Management Studio.

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

• To monitor the flow of SQL debugging information over the network, you can use the Microsoft Network Monitor, which is available at the Microsoft Download Center (https://www.microsoft.com/downloads/details.aspx?FamilyID=18b1d59d-f4d8-4213-8d17-2f6dde7d7aac&DisplayLang=en).

As soon as 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 of using SQL Debugging features.

• Common Procedure Reference
  Describes procedures that occur in many different scenarios and samples

• Transact-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 of using 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

Tasks

How to: Debug Transact-SQL Using the SQL Server Management Studio

Concepts

Debugger Security

Other Resources

Creating SQL Server 2005 Objects in Managed Code