How to: Enable SQL Server 2005 Debugging

  • Article

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Standard

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Pro and Team

 Topic applies Topic applies Topic applies Topic applies

Table legend:

Topic applies

Applies
Topic does not apply

Does not apply
Topic applies but command hidden by default

Command or commands hidden by default.

If you have the required software, you can enable Transact-SQL debugging by performing these steps. For more information, see Software Requirements.

The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging Transact-SQL code only, no remote setup is required.

To debug remote SQL/CLR code, you must install the Remote Debugging Monitor (msvsmon.exe) on the remote SQL Server 2005 computer. Running the SQL Server setup program with the correct options will install the Transact-SQL debugging components.

Additional setup procedures are required for each SQL Server in Microsoft Visual Studio 2005 and for the database connection that is used in a debugging session. For more information, see How to: Enable SQL Debugging For a Project, How to: Enable CLR Debugging For a Connection, and How to: Enable Multi-tier Debugging.

To enable SQL debugging

  1. Install the Remote Debugging Monitor on the SQL Server 2005 machine. For more information, see How to: Set Up Remote Debugging.

  2. Set up SQL Server permissions for debugging. For more information, see How to: Set SQL Server Permissions for Debugging.

    Note

    We recommend that you debug Transact-SQL code on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session has ended or the transaction is committed or rolled back.

  3. If the SQL Server version is SQL Server 2000, configure DCOM for SQL debugging on the server for SQL debugging. For more information, see How to: Configure DCOM for SQL Server 2000 Debugging.

  4. If you are debugging on Windows XP Service Pack 2, you must do the following to configure the Internet Connection Firewall to allow remote debugging. For instructions and details, see How to: Set Up Remote Debugging.

    1. On the Visual Studio host machine, you must add Devenv.exe to the Exceptions list and open the TCP 135 port.

    2. On the remote (SQL Server) computer, you must open the TCP 135 port and add sqlservr.exe to the Exceptions list. If your domain policy requires network communication to be performed through IPSec, you must open the UDP 4500 and UDP 500 ports.

  5. When debugging SQL Server 2005, you do not have to manually start the Remote Debugging Monitor (msvsmon). If you have SQL/CLR debugging enabled and step into or hit a breakpoint, the debugger will automatically start it. If you are debuggingSQL Server 2000, see How to: Run the Remote Debugging Monitor for manual startup instructions.

See Also

Tasks

How to: Enable SQL Debugging on SQL 2000 Desktop Engines

Concepts

Debugging SQL

Reference

SQL Debugging Setup Errors and Troubleshooting

Other Resources

Setting Up SQL Debugging

Debug Settings and Preparation

Change History

Date

History

Reason

July 2010

Added note to explain why sysadmin privileges are required to debug Transact-SQL to address customer feedback.

Customer feedback.